Importing Multiple CSV Files into Seperate Tables

[Guest]

I have the script to import mutiple csv files into one table. Is there a way
to import all the csv files in a folder into seperate tables named the same
as the file name?

 

[Douglas J. Steele]

Presumably your existing code has a line like this inside your loop:

DoCmd.TransferText acExportDelim, , "TableName", FileName

You need to set "TableName" to a variable based on the filename. To strip
off the path from FileName, use Dir(FileName). In other words, if FileName
is C:\Folder\File1.csv, then Dir(FileName) will return File1.csv. You can
then remove the extension using Left(Dir(FileName), Len(Dir(FileName)) - 4)

 

[Guest]

Here is my code with the fix you suggested but it won't run. Can you take a
look?

Function MyFiles()

Dim strfile As String
Dim strPath As String

strPath = "G:\2005 Inventory Balances"

If Right(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If

ChDir strPath

strfile = Dir("*.csv")

Do While Len(strfile) > 0

DoCmd.TransferText acImportDelim, "Comma", Left(Dir("*.csv"),
Len(Dir("*.csv")) - 4), strPath & strfile, true

strfile = Dir

Loop

End Function

 

[Douglas J. Steele]

Function MyFiles()

Dim strfile As String
Dim strPath As String

strPath = "G:\2005 Inventory Balances"

If Right(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If

strfile = Dir("*.csv")
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "Comma", _
Left(strfile, Len(Dir(strfile)) - 4), strPath & strfile, true
strfile = Dir
Loop

End Function

Note that I removed the ChDir statement: it served no useful purpose. (Also
why if you've hard-coded strPath don't you just put the slash at the end of
it?)

 

[Guest]

Thanks Doug!

I don't always remember to include the slash.

 

[Guest]

The code runs and imports the first file and stops??

 

[Douglas J. Steele]

What's the actual code you've got? I can't see any reason why the code below
would stop after one file.

 

[Guest]

I copied and pasted the code from your response. In order to get Jet
working, I start importing the first csv file and choose my spec file "Comma"
and cancel the import. I then kick off the code and it only runs once???

 

[Douglas J. Steele]

I don't know what you mean by "In order to get Jet working".

 

[Guest]

If I run the code from the PE window, nothing happens. If I create a Macro
and click Run, nothing happens. However if I try to import a csv file and
cancel midway, I can click Run and the code runs??

 

[Douglas J. Steele]

What are you calling "the PE window"? Do you mean the Debug window (access
through Ctrl-G)?

Try single-stepping through the code. Put a break-point in the code by
clicking in the left-hand margin beside a valid line of code (such as
strfile = Dir("*.csv")) and see what's going on.

 

[Guest]

I am looking at doing the same function, could you please send the script to
import multiple csv files into a seperate table