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
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
' Uncomment out the next code step if you want to delete the
' csv file after it's been imported
' Kill strPathFile
strFile = Dir()
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.