import

[slyvsspy]

I have 100 text files to import into a single database. How can I import them all instead of doing each single one?

Thanks,
Josh

 

[Travis]

How are they formated? Fix Length? Deliminated?

Are they all the same?

Do you want them in more then one Table?


There is the Open function and the TransferText function.

 

[slyvsspy]

They are comma delimited. But with transfer text you can only do one file at a time and I don't want to do each one of a thousand files. I want to put all the files into one table on one import.

Thanks for any ideas,
Josh

 

[Travis]

To do that you would have to append every file together.

A better method would be to use a For...Next Loop or a Do While Loop to go thru the files and loop the single TransferText line of code. You will need to set the file as a Variable so that it would change.

 

[antomack]

Are the 100 file names fixed and what about location of such?

If these are fixed then just create a table to store the location string, e.g. C:\Down\file001.txt. Just enter a record for each of the 100 files.

Then you need to write some code to loop through the table and import each of these files. Since importing will overwrite the contents of the table that were there previously you will in effect need to import the files into one table and then do an append to your main table.

Create a function with the following code, calling it should run through the imports. Change the table names to whatever suits
ImportList - list of import files locations
ImportTemp - temporary table to import files to
ImportMain - the main table where all imported details append to


Dim db As DAO.Database
Dim rstImpLst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT ImportList.* FROM ImportList;"
Set rstImpLst = db.OpenRecordset(strSQL)
rstImpLst.MoveFirst
Do While Not rstImpLst.EOF
strImpFile = rstImpLst.Location
DoCmd.TransferText acImportDelim, , "ImportTemp", strImpFile
strSQL = "INSERT INTO ImportMain SELECT ImportTemp.* FROM ImportTemp;"
db.Execute "strSQL"
rstImpLst.MoveNext
Loop