DataWright Information Services

Consulting and Resources for Excel and Access

Importing text files into Access

Many databases use data sourced from text files (.txt, .csv. tab, and other formats). Maybe you get an extract from an enterprise system, or collaborators or staff members send you daily or weekly updates in a set format.

This page shows you how to set up an import specification, and import as many files as desired from a folder of your choosing. There is also an optional step to archive imported files to another folder, so that they don't get imported twice.

Creating an import specification

In Access, go to File > Get External Data > Import. This will launch a wizard with a number of steps.

Browse to the folder that you want to use for the import, select a file, and click Import. This is a tab-delimited file, so select Delimited and click Next.  import step 2
Choose the delimiter. Also check First Row Contains Field Names, then Next. import step 3
Select the destination table if you already have one. Before clicking Next, click Advanced. import step 4
Now we can check the import specification. Fine-tune any settings (data types, whether or not to to skip the field), then click the Save As button. create the spec, step1
Use the suggested file name, or type your own, then click OK. This returns you to the wizard. create teh spec, step 2
Step 5 of the wizard. Click Next -- we have already defined the import. import step 5
Access prompts you for a primary key. Assuming that we want to use the first field, select the second option and pick Field1 from the list. import step 6
At the final confirmation step, click Finish. import step 7
Access lets you know that the import is complete. import complete

Going through the manual import wizard isn't difficult, but repeating those steps every time is tedious, which is why we defined the import specification. We can use that specification in the code that imports all our files; by setting it up correctly we can ensure that the data will be imported as we want.

The code

This code needs to go into a new module. Press Alt+F11, Insert>Module, paste the code, and save the module as basImport.

Function ImportCSVFiles()
   Dim FilesToProcess As Integer
   Dim i As Integer
   Dim bArchiveFiles As Boolean
   Dim sFileName As String
   Dim sOutFile As String
   Const TOP_FOLDER = "H:\Test" 'adjust folder name to suit
   Const ARCHIVE_FOLDER = "H:\Test\Imported" 'adjust folder name to suit
   Const DEST_TABLE = "tblUsers" 'change to suit
   Const IMPORT_SPEC = "CSV_Import_Spec" 'change to suit
   Const PATH_DELIM = "\"

   'set to False if you DON'T want to move imported files to new folder
   bArchiveFiles = True 

   'the FileSearch object lets you search a folder and, optionally its subfolders, 
   'for files of a defined type. It loads the names of all found files into an array, 
   'which we can use to import those files. 
   With Application.FileSearch
     .NewSearch
     .LookIn = TOP_FOLDER
     .SearchSubFolders = False 'we only want to search the top folder
     .FileName = "*.csv" 'change this to suit your needs
     .Execute
     FilesToProcess = .FoundFiles.Count

     'check that files have been located. If not, display message and exit routine.
     If FilesToProcess = 0 Then
       MsgBox "No files found, nothing processed", vbExclamation
       Exit Function
     End If

     For i = 1 To FilesToProcess
       'import each file
       DoCmd.TransferText acImportDelim, IMPORT_SPEC, DEST_TABLE, _
         .FoundFiles(i), True
       'archive the imported files
       If bArchiveFiles Then
         'code for archiving imported files...
         sFileName = StrRev(Left(.FoundFiles(i), Len(.FoundFiles(i)) - 4))
         sFileName = Left(sFileName, InStr(1, sFileName, PATH_DELIM) - 1)
         sFileName = StrRev(sFileName)
         sOutFile = ARCHIVE_FOLDER & PATH_DELIM & sFileName & " " _
           & Format(Date, "yyyymmdd") & ".csv"
         FileCopy .FoundFiles(i), sOutFile
         Kill .FoundFiles(i)
       End If
     Next i
   End With
End Function

'The StrRev function reverses a text string. We are using it here to simplify 
'extracting the file name: once the full path is reversed, we can pull out everything 
'to the left of the first path delimiter. Reversing this string gives us the file name.

'Note: VBA has a StrReverse function that you can use instead of this custom function.

Function StrRev(sData As String) As String
   Dim i As Integer
   Dim sOut As String
   sOut = ""
   For i = 1 To Len(sData)
      sOut = Mid(sData, i, 1) & sOut
   Next i
   StrRev = sOut
End Function

Running the code from a form

To run the code from a form, add a button to a form with the wizards turned off. Then do the following:

  1. Right-click the button, select Properties, and select the Events tab.
  2. Double-click the blank line next to On Click, so you see [Event Procedure]. Then click the Builder (...) button at the end of the line, and you will be in the code window.
  3. In the blank row before End Sub, type basImport.ImportCSVFiles. The finished code will look like this (except the first line will have the name of your command button):
Private Sub Command0_Click()
   basImport.ImportCSVFile
End Sub

Each time you click the button, the code will import all CSV files in the target folder. If you leave bArchiveFiles = True, those files will be archived to a designated folder and time stamped. That way, you don't import the same file twice.