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. Apologies to anyone from outside of the UK that may not be able to access these files I hope the demonstration is still useful. For those wishing to try pleased follow the links.
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 DoCmd.TransferText _ TransferType:=acImportDelim, _ TableName:=strTable, _ filename:=strPathFile, _ HasFieldNames:=blnHasFieldNames ' Uncomment out the next code step if you want to delete the ' csv file after it's been imported ' Kill strPathFile strFile = Dir() Loop 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.