Importing 000's of files into 1 Access table 1

[Donny]

Trying to get historical data into my database. I have 2000 files with identical structure. The files are all in the same directory with the same .txt extension and alpha numeric file names (non sequential).
I want to automate the importation of all these files into the one table - how do i do it? Is it possible?

 

[Muttley]

If the structures are exactly the same, you could try combining into one big text file via DOS using the copy command and appropriate use of wildcard placeholders.

The syntax would be something like:

copy filenames targetfilename

Or you can use the type command if you are going to be adding new files from the source.

type filename >> newfilename (if the file doesn't already exist)
type filename > newfilename (if the file does already exist)

If you want to automate you can probably use the shell command in VBA to run the above (although haven't tried this myself).

Hope this helps - if not, try using Docmd.TransferText from VBA.

Muttley

 

[BlackKnight]

Hi,
There is no need to append all of the text files together since it would slow rthw process down needlessly. Use the docmd.transfertext to import the files. To get each file name simply use the dir$ statement to get all of the text files in the folder where they are located. Put all of this logic into a do-while loop. Create a command button on a form called Import and put all the logic into its on click event.

Have a good one!
BK

 

[MichaelRed]

Personally, I would do a variation on BK's approach. Import each, do some 'validity' chhecks on the information. Correct what can be corrected. Move the ones which cannot be made to 'fit' the requirements into a "tblBAD". Append the remainder (the 'good ones') to a "tblGood".

With 2K files, there WILL be some problems. You should groom the data to minimize the errors in the working table, and save the bad ones to a seperate table. They MAY be recoverable through other efforts, but only if you keep track of the record and where it came from.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[BlackKnight]

Michael,

Yes, data validation checks when importing is implied as is an exception table.

Have a good one!
BK