COURSE of the MONTH

VBA to Import Data from a tab delimited text file into certain fields of an access table automatically

Hi,

I have a tab delimited text file with some data which I need to use to populate certain fields in a table in access by writting a macro in VBA. The data is not in the same order as the fields in the table. So, I was thinking would it be a beter approach to write a macro which read the text file and store each tab delimited data into a separate variables and then write the value of required variables in appropriate fields in the table.

I dont really want to create another temporary/intermediate table in access to write the data from the text file in teh same order and then insert the required value into the final table.

For Ex: Text file contains:

12345 achuyoo 25/0304 hjyuiii 458896 ICL 0 23/05/04 9

And we might need to store this data in the following manner in Access

Normally I link a table to such a text file and use an append query to "move" the fields to the appropriate fields in the target table.

Idea ?

Nic;o)

0

PCMENIACAuthor Commented: 2004-04-30

Hmm..

Rockiroads, My first reaction was to do the same but then had comlplications as we are trying to avoid to create new tables. Also, what I am trying to do is to create a button on a form which when clicked, reads the files and gets the values. This is because we get a new file every week but with the same name.

But I'll give your idea a shot. I'll let you know how it goes in an little while.

To link a table to a textfile just create a new table and select the option to link.
On the Open dialog select as filetype .txt and navigate to your file.

After the ceation of the link open the query editor, select this linked table and the needed fields. Next change the query type into an append query and select the target table. Equally named fields will be "linked" automatically by Access, but just every "move" is possible.

Save that query and all needed is to run the query by a double-click.
You can even replace the textfile with a newer version (as long as the name remains the same) and rerun the query to get new data imported.

All without a single line of code.

Nic;o)

0

PCMENIACAuthor Commented: 2004-04-30

Nico,

There's nothing wrong with your approach as well but I was under the impression that the "linking" approach would fail if the file gets updated regularly.

But there's a slight problem that after linking the text file, date parameters got changed from 30/04/04 to 2030-04-04. How can I stop this?? if I change the parameters during linking process to text, then it transfers date in the exact format but then If I try inserting text from this temp table into a date/time parameter in the final table, the nwould it not create a problem??

Sorry, just too cryptical as usual, this is referring to a "field cell" in the graphical query editor.
In SQL text you would find:
select dateserial(mid(fieldx,7,2),mid(fieldx,4,2),mid(fieldx,1,2)) as DateReformat, ...