1000's of text files!

[Gabriel]

I have thousands of .txt files that I need to import into a table. I can’t go to the import feature on the menu because it would take forever.

It gets even worse.

The text has spaces not commas. Like this:

10004 Mini-calculator. Super long-life battery. $3.49

or

10005 Get your figures right . . . and right on time! LCD calculator/alarm watch. $9.95

So I need the text before the first space to go into one field (IDnumber [all of the numbers have 5 places]) and the text behind the last space to go into another field (price[all of the prices have a $ but not all have three numbers]) and all the rest of the text to goes into the third field (product info).


Thanks for the help! I love you guys!

Gabriel!

 

[Fornatian]

I would import it into excel first and use the Text to Columns command to split the data at the spaces.

Is your naming convention consistent for the txt files if so you could run a do...loop to scroll through the files one by one importing the data.

Sorry, I don't knwo the exat syntax to pass on.

Ian

 

[John Bull]

you could try the transfertext option.

 

[Gabriel]

I think I might be in over my head. Thanks for your info guys. I'm a bit new at this, do you have any more detailed information on how I might get this done?

Thanks again

Gabriel

 

[Jack Cowley]

Using the Left, Right, Mid and InStr Functions you can pick apart your text fields and get the data you want. For Example Left(TextString, 5) will return the ID number. By writing some code you can extract the data you need, loop through all the text strings you have and fill your table in one go. This article will give you examples of how to get the parts of the string that you need. http://support.microsoft.com/support/kb/articles/Q115/9/15.asp?LN=EN-US&SD=gn&FR=0

HTH,
Jack

[This message has been edited by Jack Cowley (edited 11-18-2000).]

 

[Atomic Shrimp]

the best way I have found to import a bunch of text files without processing any of them twice or missing some is to use the DIR function to get the name of the next matching file, use TRANSFERTEXT to import it and then use NAME to move it into another filder out of the way; repeat the process until the DIR function comes back with "".

By the way, you're going to have quite a challenge if your file is space-delimeted and you also have a variable number of spaces within some of the text fields therein - you import routine isn't going to be able to discern which of the spaces are field breaks and which are just spaces.

The usual way round this would be for the application that exports the data to enclose the text within inverted commas - do you have the option to export the text again in a more import-friendly format?

Mike

 

[Gabriel]

Thanks All!

I think your help will go a long way. Mike - no I cant get the files in any other format. I wish. If you have any other thoughts, please leave them. Thanks!

 

[Atomic Shrimp]

If you're still working on this one, I've worked out a way that would enable you to determine where the LAST space in a text field is - have a look at: http://www.access-programmers.co.uk/ubb/Forum3/HTML/000919.html

Hope this helps

Mike