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.

Choose the delimiter. Also check First Row Contains Field Names, then Next.

Select the destination table if you already have one. Before clicking Next, click Advanced.

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.

Use the suggested file name, or type your own, then click OK. This returns you to the wizard.

Step 5 of the wizard. Click Next — we have already defined the import.

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:TestImported" '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.

 
Home | About |
Links |

Contact | Excel |
Access