Import multiple text files into separate tables all have different specifications

Question

I need to import multiple text (csv) files into multiple tables in access. At the moment I do it one by one using External data, browsing to the file, importing it, setting specifications and saving it as a table. It's tedious. I've found plenty of code which let me import multiple files to multiple tables. My issue is that each import has different specs.

I have this code which works but it still means that i have to select table one then go back and select table two then go back and select table 3 etc.

Is there a way I can loop through the directory, and import the files with their specific spec? Table names never change, spec for each table never changes. They need to be separate tables. Any and all help gratefully recieved!

Private Sub Command1_Click()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String

'In case we have to run everything more than once, make sure table is clear before appending data
DoCmd.SetWarnings (False)
DoCmd.RunSQL "DELETE * FROM " & "TABLEONEHeader"

'Explorer window opens
With Application.FileDialog(msoFileDialogFilePicker)
'Only show CSV files
.Filters.Add "Text Files", "*.csv", 1
.Title = "Select TABLE ONE..."

'Prevent user selecting multiple files.
.AllowMultiSelect = False

'If the users selects a file use it, otherwise show an error message
     If .Show Then
         strFile = .SelectedItems(1)
     Else
         MsgBox "You haven't selected a file.", vbCritical
     End If
 End With
'Import data to table using specific specifications
DoCmd.TransferText acImportDelim, "TABLEONE", "TABLEONEHeader", strFile, True
MsgBox "TABLEONEHeader has been imported"


'move on to next file
'In case we have to run everything more than once, make sure table is clear before appending data
DoCmd.SetWarnings (False)
DoCmd.RunSQL "DELETE * FROM " & "TABLETWO"



'Explorer window opens
With Application.FileDialog(msoFileDialogFilePicker)
'Only show CSV files
.Filters.Add "Text Files", "*.csv", 1
.Title = "Select TABLE TWO file..."
'Prevent user selecting multiple files.
.AllowMultiSelect = False

'If the users selects a file use it, otherwise show an error message
     If .Show Then
         strFile = .SelectedItems(1)
     Else
         MsgBox "You haven't selected a file.", vbCritical
     End If
 End With

DoCmd.TransferText acImportDelim, "TABLETWO", "TABLETWOHeader", strFile, True
MsgBox "TABLETWO has been imported"

'move on to next file

Do the files have any identifier as to which import spec to use, such as part of the name? If so, you could use an `If\ElseIf`End If` block. Otherwise, you would probably need to use VBA to open the file, read one line in and work out which file type before running the import. — Applecore, Jan 11 '23 at 10:21

Spencer Barnes · Answer 1 · 2023-01-11T10:51:21.603

I recommend you make a subroutine that takes parameters, like the below - then call this multiple times from another sub. For example:

Sub Import_Table(TableName as String, FileFullPath as String)
With DoCmd
    .SetWarnings False
    .RunSQL "DELETE * FROM " & TableName & "Header"
    .SetWarnings True
    .TransferText acImportDelim, TableName, TableName & "Header", FileFullPath, True
End With
MsgBox TableName & " has been imported.", vbInformation
End Sub

For the other part of your question, looping through files in a folder is easily done using a Do Until Loop and the Dir function - I can't write this code for you not knowing the exact names and paths of the different text files you want to import, but here's a trimmed-down example.

Sub Loop_through_text_files()
Dim FileNum as Long: FileNum = 1
Dim FileLocation as String: FileLocation = "C:\Users\Me\Documents\Testing\"
Dim FileName as String

Do
    FileName = Dir(FileLocation & FileNum & "*.csv")
    If FileName = "" Then Exit Do 'Will stop looping when it doesn't find a relevant file

    Import_Table _
        TableName:="Table_" & FileNum, _
        FileFullPath:=FileLocation & FileName
    FileNum = FileNum + 1
Loop

End Sub

The above will look for .csv files in the folder "C:\Users\Me\Documents\Testing" that have 1, 2, 3 etc in their name, and import that to tables called Table_1, Table_2, Table_3 etc.
Note that 1, 2, 3 is different from "ONE", "TWO", "THREE" so you may have to adapt your naming protocol to deal with this. If that's not an option, see here for a handy function on converting numbers to words.

Your Single-Line `If`-statement using continue characters to split it to multiple lines looks quite confusing... — FunThomas, Jan 11 '23 at 10:45
That's fair, I've edited my answer to split it out. I like to use continue characters to split named parameters when calling subroutines just for readability, but I shouldn't have made that a single-line `If`. — Spencer Barnes, Jan 11 '23 at 10:47
That's a great help, thank you! I'll have a go over the next few days and see if I can get something working. — MHM, Jan 11 '23 at 12:56

Import multiple text files into separate tables all have different specifications

1 Answers1