Month: May 2018

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. 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 please follow the links.

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 administration excercise to request a data download involving filling out your name you should be sent an email link to initiate a data download. The download consists 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
        ' Now place the location of where the csvs are within the brackets below
        strPath = "C:\Users\Mark\Documents\CodePoint\Data\CSV\"

        ' 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.

MS Access Function – Scan through a directory and write list of files to a table

Pretty much as the title

Ensure you have a table called tblFiles with one field called Filename

Here I am finding all files in folderspec = “C:\Users\Mark\Documents\CodePoint\Data\CSV”

Alter as appropriate

Public Function ShowFolderList()
Dim fs, f, f1, fc, s
Dim rs As DAO.Recordset
Dim folderspec

Set rs = CurrentDb.OpenRecordset("tblFiles")

folderspec = "C:\Users\Mark\Documents\CodePoint\Data\CSV"

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files

For Each f1 In fc
    rs.AddNew
    rs.Fields("FileName") = f1.Name
    rs.Update
Next

Set rs = Nothing

MsgBox "Finished"

End Function