Importing Large Numbers of Tables from Text files.

[gollumullog]

Good Day,

I have been having this issue for quite a long time, and have yet to find an easy/elegant solution.

I am trying to create tables in an Access database.

I have these tables as CSV files, with the Header as the first line.

I have approximately 1000 tables.

Importing these tables by hand is a pain.

I figured there must be a simple way to import all tables in a folder using a Macro.

I created a form with a button that runs the following Sub:

Private Sub Command0_Click()
Dim InputDir, ImportFile As String, tblName As String
Dim InputMsg As String

InputMsg = "Type the pathname of the folder that contains "
InputMsg = InputMsg & "the files you want to import."
InputDir = InputBox(InputMsg)
' Change the file extension on the next line for the
' type of file you want to import.
ImportFile = Dir(InputDir & "\*.txt")

Do While Len(ImportFile) > 0
' Use the import file name without its extension as the table
' name.
tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1))
' Change dBase III on the next line to the type of file you
' want to import.
DoCmd.TransferText acImportDelim, "MySpecs", tblName, ImportFile, Yes
ImportFile = Dir
Loop
End Sub

The problem is that unless every table is exactly the same you get an error, because the spec: "MySpec" needs to exactly define the table you want to import.

Am I missing something simple? Is there an easy way to do this?

I would think that becasue the defaults when importing a text file work, why can't you just import using the TransferText method and use the defaults....

Please help, this is really frustrating.

Cheers

[MMcCarthy]

There isn't an "easy" way to do this but you can do it using code. Have a look at this tutorial.

Import csv (comma separated value) file