export then import to deal with delimiters

[nowanda]

Hello All!

I need a place where I can get some information on exporting to text files and importing from text files programmatically into a pre-existing Access database. The issue: I have a field with semi-colon delimiters which I'd like to do some calculations and date formatting- so I would prefer to store this information separately.

COMMON_DATA
-4.5;12/25/2002;34;2.6;
3.6;01/06/2003;;;
18;02/09/2003;23;-5.8;
1.7;03/04/2003;28;5.7;

I tried using :
Left$([COMMON_DATA], Instr([COMMON_DATA], ";")-1)
and varying Mid's as well but the data entered will always be 4 semi-colons in total and I always seem to get extra semi-colons in my query results. I'd love to do this as a query - but so far the easiest way has been to physically export the field to a text file then import it using a semi-colon delimiter... Can this be done in a series of commands to pseudo-automate it - or have I gone off the deep end?

A little cybernetic push would be greatly appreciated!

Nowanda

 

[Parra]

Maybe I don't understand your question, but you can create Macros in Access to export your queries as a text file, and you can also create queries to import text files into tables. Is this what you were looking for?

The macros would automate what you are manually doing.

 

[nowanda]

Hello!

It would be a macro that I'd be looking to create.. or even - if possible - maybe avoid the text file altogether - and if possible use a query to separate out the ";"'s and then insert the new fields into a new table?

Thanks!

Nowanda

 

[gator]

This might not be what you're looking for, but have you tried to import the text file as a table and set up the import specifications and then save them. I do this every month at work and would be glad to walk you through it if you need more help/information.

-gator

 

[nowanda]

Thanks gator!

This works pretty well for me.. I had also fiddled around with my original queries and might have hit the jackpot as well!

Curious -> can you call the saved parameters through a VBA module?


Thanks!
Nowanda