Unanswered: How do I import many txt file datebases?
I was given a folder with 17 thousand text files. Each file has a main number, and then a verticale list with 3 pieces of information on each line divided by a comma. I need to get all the information in those 17 thousand files, into 1 database. How?
Hopefully these files have names such as file00001, file00002, file00003, etc. If so, it should not be too tough. You can make the table you want to import them into, do the first one, as your using the import wizard, click on advanced, and save the method you are using as an import protocol. This will make sure that it imports each file the same, if you are not so comfertable with this part, you will see, its pretty simple. After saving your import protocol you will need to do the vba. You will need to set up a loop to go through all the files, and import each one into your table using the protocol, and it should look something like this:
x = 00001
Do until x=17000
DoCmd.TransferText acImportDelim, "Import Protocol Name Here", _
"Your Table Name Here", "C:\...\file"& x &".txt"
x = x + 1
You don't necessarily have to use the import function to process the file. What I do is I create a File Specification (what rickinin called a protocol) then I write code to link to the file (use the same method but use acLink as the first parameter). When you link to the file, give the new table name the same name for each file. Then base queries (Append, Select and Update queries) on the table.
Start by creating the table that you need all of the data to be in (this could be one or more tables). Link to one file and call the resulting table tblIncomingData. Then create a few queries to transform the linked data to the table(s) in your database. Then write some code that will loop through the files in your directory (File001.txt, File002.txt, File003.txt) and link to each file one at a time and call the table tblIncomingData. Then create more code to run the queries that need to be executed on each file.
You will want to look at the following functions/methods: