By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,701 Members | 2,006 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,701 IT Pros & Developers. It's quick & easy.

Importing Large Numbers of Tables from Text files.

P: 1
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
Sep 19 '07 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Sep 30 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.