Importing multiple files from excel into a table and also inserting the file name into a column

[JoeM]

I dont know why this is happening, because I dont have a column 'creation_ts'

I suspect that one of your files has something like that in there.
I would open each file, one-by-one, and do a CTRL-F (Find) and search for "creation_ts" and see if it finds it in any of your files.



You may want to try the script on the test files that you sent me (since that worked for me). If that works, then you have at least confirmed that the program/script is working as intended, and the issues is with one of your data files.

[Mary87]

Thank you very much Joe .
It is working

[JoeM]

You're welcome!
Did you find out what the issue was?

[Mary87]

Yep, the module was trying to read from a different spreadsheet...

I have noticed, however when running the module the following error appears:
"Run-time error '3274'
External table is not in the expected format".
However when running with the spreadsheets open in excel the module runs fine....
Btw all my files are in .CSV so have amended the code:

"strFile = Dir(strPath & "*.csv")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames
DoCmd.RunSQL "UPDATE [AGLVsInvoice] SET AGLVsInvoice.[File Name]= '" & strFile & "' WHERE AGLVsInvoice.[File Name] IS NULL OR AGLVsInvoice.[File Name]='';"
' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile
strFile = Dir()
"

Sorry for bothering you again....

[JoeM]

I think you just unanswered your own question (but just don't realize it!)

Btw all my files are in .CSV

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

CSV files are text files. You typically use TransferSpreadsheet when importing Excel files, not text files.
Now, since you can open CSV files in Excel, it is probably clumsily making it work, but not before spitting out a few errors telling you that it is expecting an Excel file, and importing a text file.

The most proper thing to do would be use TransferText into of TransferSpreadsheet. You just need to set up an Import Specification to do that. That is not hard. Just go through the process of manually importing one of your files. This will invoke the Import Wizard. Go through it, indicating the format of each of the fields. And at the very last step, before clicking "Finish", click "Advanced" and do a Save and give your Import Specification any name you want.

Then, use that name in the TransferText action under the Import Specification argument.

[Mary87]

Hi Joe,

Sorry for the late reply. Thank you v much for your suggestions :-).
I have decided to use the old code that you suggested because it is working and also it is a one off exercise.

Once again thank you v much