MS Access Function – import all CSV files from a directory with the same structure into a single table

This is a really nice function that can be used to place all data from multiple CSVs (with the same structure) into a single table.

Here I use the Ordnance Survey’s excellent Code Point data set that gives postcodes in the UK along with eastings and northings as an example – This lists each postcode in the UK along with further administrative categories. As it is open data I list the links so you can get the same data if you want to follow along.

After download you will see the problem each postcode is in a separate CSV

Ordnance Survey Open Data Code Point UK Postcodes

After a short process to request the download including filling out your name you should be sent an email to download the data. This will consist of a zip file of two directories one named DOC one named DATA the DATA directory contains a subdirectory called CSV which at May 2018 for my download consisted of 120 csv files.

Opening a single file ( in this case Edinburgh eh ) we see

I’ve already figured this out here , but there are 10 fields here (some are blank in my example)

Here I create a table called T01CodePointCombined with 10 fields marked
F1 through to F10
Note if you don’t create the table this function is so powerful it will do it for you

I then create a module and ensure that all the CSV files I wish to import are in a single directory here “C:\Users\Mark\Documents\CodePoint\Data\CSV\”

Public Function ImportAllFiles()

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in csv file
' has field names
blnHasFieldNames = False

' Replace C:\Users\Mark\Documents\CodePoint\Data\CSV\ with the real path to the folder that
' contains the csv files
strPath = ""

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "T01CodePointCombined"

strFile = Dir(strPath & "*.csv")
Do While Len(strFile) > 0
strPathFile = strPath & strFile

DoCmd.TransferText _
TransferType:=acImportDelim, _
TableName:=strTable, _
filename:=strPathFile, _

' Uncomment out the next code step if you want to delete the
' csv file after it's been imported
' Kill strPathFile

strFile = Dir()

MsgBox "Finished"

End Function

Points to note make sure all csv are closed when you run it. That’s about it takes less than 5 minutes to move all the records from those 120 files into a single table within an MS Access Database.
After import if it’s gone correctly you should have in the region of 1.7 million records in T01CodePointCombined.

Author: Mark

Mark Brooks a forty something individual working and living in and around Edinburgh