Importing into Access using Autoexec Macro

[RamLaramie]

I need help! I am trying to import a text file into Access via an autoexec macro. I also need to parse the data (Delimited by **)into a fixed table. I have some very rough ideas on this, can anyone help. It would seem to be a very simple process, but is becoming more challenging the more I get into it. By the way, the .txt file is coming out of Lotus Notes R5. Thanks

"You never get a 2nd chance on 1st impressions"

 

[psemianonymous]

This is how you do it.

1. Begin to import your text file manually using File->Get External Data.
2. Set up your import so that it gets the text file as perfect as you want it (may require some trial & error).
3. Don't finish the import! Click on .. I believe it is Advanced...Then save your report specs. Name it something meaningful, i.e. something you can remember.
4. In your autoexec macro, do a TransferText and set up the filename, the report spec you have just now created, and the destination table.

 

[RamLaramie]

O.K., thanks. This is a great help. But what about parsing the data. I have one field in the .txt file that I need to split into 5 seperate fields, is this possible? When the file comes from the Lotus Notes R5 db, the five check boxes are dumped into one field and seperated by **, is it possible to split these (5) into seperate fields within Access?

"You never get a 2nd chance on 1st impressions"

 

[WildHare]

I don't think your original import process will be smart enough to parse out the one 'chunk' into five. But it should be a snap to write a query that operates on the imported table to do it for you. Just think in terms of two separate steps:

1) import the file to a table.
2) run a query (to perhaps "make" another table?) to parse out your original data into the form in which you want it finalized.


hth

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm