import multiple CSV file into Access database and save them into different tables based on the file name

Question

I've got about 100 CSV files that I'm trying to import them into Access and then rename the tables based on the file names.

Here is the code I've found but the "tablename" should be my file name. however, I can't get it to work as I'm new to scripting.

Function Import_multi_csv()

Dim fs, fldr, fls, fl
Set fs = CreateObject("Scripting.FileSystemObject")
Set fldr = fs.getfolder("D:Files\")
Set fls = fldr.files

     For Each fl In fls

      If Right(fl.Name, 4) = ".csv" Then
       DoCmd.TransferText acImportDelim, , "TableName", "D:Files\" & fl.Name, False
      End If

     Next fl

End Function

Also, I have three columns in my files and I want the third column to be imported as a double.

Any help will be appreciated.

Welcome to Stack Overflow! Please don't ask double-barreled questions (also), because these might need 2 fully separate answers. — Erik A
– Erik A, Commented May 1, 2018 at 11:28

Gustav · Accepted Answer · 2018-05-01 11:33:45Z

1

It should be this:

DoCmd.TransferText acImportDelim, , "[" & fs.GetBaseName(fl.Name) & "]", "D:Files\" & fl.Name, False

As for your second question, you could create, save, and use an import specification.

answered May 1, 2018 at 11:33

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

import multiple CSV file into Access database and save them into different tables based on the file name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related