Importing Multiple CSV Files into Seperate Tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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)
 
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
 
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?)
 
What's the actual code you've got? I can't see any reason why the code below
would stop after one file.
 
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???
 
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??
 
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.
 
I am looking at doing the same function, could you please send the script to
import multiple csv files into a seperate table
 
Back
Top