Import mutliple CSV files into one Access table (1 Viewer)

[tezread]

I have over 100 CSV files all with the same format and structure. I have imported one of those into a new table in Access but I do not want to have to do this one by one for each CSV!. In a bid to solve this I searched for a piece of code that could handle batch imports.

Private Sub Command3_Click()
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim filename As String
Dim path As String

DoCmd.SetWarnings False
path = "C:\Test\"
'Loop through the folder & build file list
strFile = Dir(path & " * .csv *")
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
End If
'cycle through the list of files
[B]For intFile = 1 To UBound(strFileList)
[/B]filename = path & strFileList(intFile)
DoCmd.TransferSpreadsheet acImport, codepointimport, MyTable, filename, True
Next intFile
DoCmd.SetWarnings True

End Sub

the run time error I get is run time error 9 'subscript of of range' The culprit is highlighted in the code above.

any ideas what is causing this?

 

[gemma-the-husky]

try

For intFile = 0 To UBound(strFileList) -1

arrays are zero-based

 

[tezread]

try

For intFile = 0 To UBound(strFileList) -1

arrays are zero-based

Hi gemma

tried that and still get the same error

 

[tezread]

this time

filename = path & strFileList(intFile)

was highlighted

 

[PeterF]

I don't know if it's a copy paste problem but in the dir function there are spaces for the file filter so you get a empty list.
The check for a empty list fails because there's no else, your code should look like:

If intFile = 0 Then
   MsgBox "No files found"
else
   'cycle through the list of files
   For intFile = 1 To UBound(strFileList)
   filename = path & strFileList(intFile)
   DoCmd.TransferSpreadsheet acImport, codepointimport, MyTable, filename, True
   Next intFile
End If

 

[gemma-the-husky]

i looked again - what PeterF means is this

strFile = Dir(path & " * .csv *")

should be this

strFile = Dir(path & "*.csv")

make sure path has a trailing \ also

 

[tezread]

Hmmm. this is strange. Now have

"action or method requires a tablename argument"

If intFile = 0 Then
MsgBox "No files found"
else
'cycle through the list of files
For intFile = 1 To UBound(strFileList)
filename = path & strFileList(intFile)
[B]DoCmd.TransferSpreadsheet acImport, codepointimport, MyTable, filename, True[/B]
Next intFile
End If

My table name is MyTable

 

[tezread]

Weird I changed the Do.cmdTransferSpreadsheet to

[B]   DoCmd.TransferSpreadsheet acImport, acSpreadhseetTypeExcel12Xml, "MyTable", filename, True[/B]
[B]

[/B]

and now I get

error 3170
"could not find installable ISAM"

 

[gemma-the-husky]

if you have a csv, why try and import it as xml

that cannot be correct

you want transfertext for csv files

 

[tezread]

if you have a csv, why try and import it as xml

that cannot be correct

you want transfertext for csv files

If intFile = 0 Then
   MsgBox "No files found"
Else
   'cycle through the list of files
   For intFile = 1 To UBound(strFileList)
   filename = path & strFileList(intFile)
   DoCmd.TransferSpreadsheet acImport, TransferText, "MyTable", filename, True
   Next intFile
End If
End Sub

gives same error message "could not find installable ISAM"

 

[gemma-the-husky]

I meant

DoCmd.TransferText etc

 

[tezread]

Thanks Gemma. I wondered, I have specified the Import specs here

   DoCmd.TransferText acImport, ImportSpec, "MyTable", filename, True

It is ImportSpec. Now when I execute the code I get

Field 'AL1 1AE' doesn't exist in the destination table'
What is happening is that all 120 CSV files have not had a field name allocated to them (yet) I thought that if I had referenced the Import specs it would have handled that bit for me but obviously not. Is there a way I can overcome this as i don't want to have to go in each of the CSV files and add the field names

 

[gemma-the-husky]

do it manually, rather than with code, and you should see the problem

one option is to regard the first row of the file as data, or column headers. I suspect you have the wrong option for this.

 

[tezread]

do it manually, rather than with code, and you should see the problem

one option is to regard the first row of the file as data, or column headers. I suspect you have the wrong option for this.

Hi Gemma

I did try it manually and can see the problem. I am not sure though how to regard the first row as data when I import the data? There is an option to choose 'first row contains headers'. I have checked/unchecked that but the same problems comes. When importing the code is looking for 'Field`' which doesn't exist in the other 119 CSV files.

 

[gemma-the-husky]

setting first row as data or not depends on whether your csv file has a header row or not. You can save that setting within your file import spec (offhand, I think) - if not, it is an option in the docmd.transfertext statement

if the csv files are not all the same, then you can't use the same import for them. Can you see the difference by inspecting the csv files?

 

[tezread]

setting first row as data or not depends on whether your csv file has a header row or not. You can save that setting within your file import spec (offhand, I think) - if not, it is an option in the docmd.transfertext statement

if the csv files are not all the same, then you can't use the same import for them. Can you see the difference by inspecting the csv files?

all the csv files are the same. None of them have row headers, the first line is data. I can't see the setting in the Import spec to say if there is a header in the first row though. How do I set up the option in the docmd.transfertext statement Gemma?

 

[tezread]

all the csv files are the same. None of them have row headers, the first line is data. I can't see the setting in the Import spec to say if there is a header in the first row though. How do I set up the option in the docmd.transfertext statement Gemma?

I found this

expression.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)

and set hasfieldnames to false

Now the error message is

Field F1 doesn't exist in MyTable

 

[tezread]

Think i crackd it. Imported first table and renamed Field1 to F1 etc and bingo!!!

 

[gemma-the-husky]

yes I just checked - the hasfieldnames is an option in the docmdstatement, not in the file spec.

the data in the csv will be imported in accordance with your file spec - so maybe you need to amend this to deal with the F1/Field1 discrepancy. F1/Field1 sounds like this is spreadsheet related.

Sounds like you are making good progress though.