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.

About Mark

Mark Brooks a forty something individual working and living in and around Edinburgh
This entry was posted in Address Matching, All, Geographical Information Systems, GIS, MS Access, Open Source, VBA Code MS Access. Bookmark the permalink.