By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,153 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Please guide me

P: 3
Dear friends , hi

I have text file with name "transPri.txt" that contains
something records like "1234324;234556090;987654"
in each line . I need a access vb code that open the file and eliminate
the ";" between fields and resave the file with the name "trans.dat"
so the new file may be like "1234324234556090987654".//

best regards
Feb 5 '08 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 446
Dear friends , hi

I have text file with name "transPri.txt" that contains
something records like "1234324;234556090;987654"
in each line . I need a access vb code that open the file and eliminate
the ";" between fields and resave the file with the name "trans.dat"
so the new file may be like "1234324234556090987654".//

best regards
Hi
This task has obviously to be broken into a number of steps; the first being to import into Access.

Your data makes this 'slightly' problematical because it uses semi-colons and not the standard Comma Seperated Variable format. This means we have to do an extra step to create a template.

So the pre-requisites are going to be;
  1. A 'destination' table to hold the imported data
  2. A simple form with a command button to run some code
  3. Path and file name of your source file e.g."c:\aaa\transPri.txt"
  4. Read Help on the "DoCmd.TransferText" command, then the following may make more sense.
Your data appears to be numeric but for this exercise I suggest you assume they are character strings; you're planning to concatonate them anyway. So when you create your table add three text fields and call them Field1, Field2, Field3 unless you have reason to call them otherwise. Save your table, e.g. tblimport

Next you have to create your 'template', which you do by by starting to import the data interactively. From the Main Menu select
File > Get External Data > Import, then navigate to your source data (you may have to select the 'txt' file extension), click OK and the Import Wizard should open.

Check 'Delimited' then click Next.
The Wizard should select semi-colons as being you delimiting characters and divided your data into columns (fields). If not then adjust.

Now click 'Advanced' because you want to save the template, not proceed manually. A new dialog box should have opened with a grid at the bottom showing your three fields. If you chose not to call then Field1 etc, you should now edit them to match your table spec. AT THIS POINT ALSO SET DATA TYPE TO TEXT.

Now click 'Save As', write down the name, "TestImport Import Specification", then click OK.

Now go to the form where you want to run this process from and under the On_Click event of the button writ something like;-
Expand|Select|Wrap|Line Numbers
  1. 'Copy data from text file into tblImport
  2. DoCmd.TransferText acImportDelim, "TestImport Import Specification", "tblImport", "c:\aaa\transPri.txt"
  3.  
Edit as required for your situation and if all goes well, the data should be imported into your table when you click your button.

The next stage is to extract the data and write it to an 'output' file. You are going to use a similar command eg;-

'Copy data from tblImport into text file output.txt
DoCmd.TransferText acExportDelim, ,"qryExport", "c:\aaa\output.txt"

This requires an Access query called qryExport. So, create a new query, add table tblImport, then in the first column of the grid concatonate the three fields thus;
Expand|Select|Wrap|Line Numbers
  1. Expr1: [Field1] & [Field2] & [Field3]
then Save the query as qryExport.

This will produce an Export file with the semi-colons removed. The file will be overwritten each time.

The output file will have quotes around the concatonated values because they are 'text' not real numbers

Good luck

S7
Feb 5 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.