Markus Blog

Importing Multiple CSV Files to MS Access Database

I got a task to convert a lot of .csv (Comma Separated File) files today. There are hundreds, 970 files to be exact of CSV files. MS Access does have feature to import CSV file to its table, but if you want to convert 970 files one by one, it will took a damn long boring time.

Luckily all the CSV files have the same column format, so what I need to do is find how to automate the process instead of doing it in barbaric way (convert it one by one). I know that this is doable with macro script.

I check on ‘Macros’ tab. From that tab you can choose several actions. As I want to import data from text, I choose ‘TransferText’. You’ll need to input the parameter below the actions table, its explanation so you should be easy to understand it, if not.. hit F1 button.

Anyway the ‘Macros’ tab didn’t suit with what I want to do as it can only read one CSV file. When I input *.csv on the file name field, the macro gives error message that it can’t find the file.

Doing some search on Google point me to this page: http://www.mcse.ms/message1634699.html ‘Nikos Yannacopoulos’ give a script to import multiple CSV files to MS Access table. I need to modify it a little bit to make it work though, here is the modification:

Function Import_multi_csv()Dim fs, fldr, fls, fl
Set fs = CreateObject("Scripting.FileSystemObject")Set fldr = fs.getfolder("D:CSVFolder\")
Set fls = fldr.filesFor Each fl In fls
 
If Right(fl.Name, 4) = ".csv" Then
DoCmd.TransferText acImportDelim, , "tblName", " D:CSVFolder \" & fl.Name, True
End If
 
Next fl

On this code:

DoCmd.TransferText acImportDelim, , "tblName", " D:CSVFolder \" & fl.Name, True

acImportDelim means that you want to import delimited file. Mind that the second parameter is left empty. The second parameter is ‘Specification Name’ where you can specify options that determine how the text imported, I don’t needed it though so I keep it empty. The third parameter is the table name, followed with the directory name including the filename. Last parameter is a Boolean, which specify whether the first row of your CSV file is the field name or not.

To execute the VBA script go to ‘Modules’ tab and create new Module, copy paste the code above and save. On top of the Module window there will be buttons to run, pause and stop your VBA script. Click on run button to start the script.

The script above does work for me, but there are some errors on exporting. I think there is slight type miss on the converting which cause some value didn’t imported. The nice thing is that MS access will generate a table which list the error messages.