Importing 58 csv files into 58 Access tables

[Deirdre]

Good morning. Yes, I know the 58 tables aren't ideal but it is the hand I have been dealt.

Each month, I will receive 58 csv files. These files will all be contained in one folder which will vary each month by name (e.g. 20170331, 20170430, 20170531, etc).

The file names will not change nor will their 4-column structure. The will always be csv.

I would like a macro that will import/append the 58 files into their respective tables.

For example:
DoNotCall.csv would be imported into DoNotCall table
DoNotEmail.csv would be imported into DoNotEmail table

I have searched many threads, but none quite seem to do exactly what I need.

Any and all assistance is appreciated.

Thank you.

 

[Joe4]

OK. Here is what I came up with.

First, create a new unbound form, and add a TextBox (named "txtFolderName") and a command button (named "cmdImportFiles").
Then, add the following VBA code to the On Click event of the command button:

Private Sub cmdImportFiles_Click()

    Dim strPath As String
    Dim strFile As String
    Dim strFullName As String
    Dim strTblName As String
    Dim strImpSpec As String
    Dim lngFileCount As Long
    
'   Enter import spec name here
    strImpSpec = "CSV_Import_Spec_Name"

'   Check to see if valid folder entered
    If IsNull(Me.txtFolderName) Then
        MsgBox "Must enter a folder name!", vbOKOnly, "ERROR!"
        Exit Sub
    Else
        strPath = Me.txtFolderName
    End If

'   Check to see if selected folder exists
    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MsgBox "No such folder name exists!", vbOKOnly, "ERROR!"
        Exit Sub
    End If

'   Loop through all CSV files in folder
    strFile = Dir(strPath & "\*.csv")
    While strFile <> ""
'       Get table name from file name
        strTblName = Left(strFile, Len(strFile) - 4)
'       Get full file path and name
        strFullName = strPath & "\" & strFile
'       Import file
        DoCmd.TransferText acImportDelim, strImpSpec, strTblName, strFullName, False, ""
'       Increment file counter
        lngFileCount = lngFileCount + 1
'       Move to next file
        strFile = Dir()
    Wend

'   Ending message
    MsgBox lngFileCount & " files imported.", vbOKOnly, "COMPLETE!"

End Sub

The only thing you should have to change in the VBA code is the name of your Import Specification name at the top.

Then, to run, simply type in your folder name where the files reside (do NOT put a slash at the very end) and click the Command Button.

Note, if you would like to add a file browser so that the user does not have to type in the folder name manually, see: Browse For Folder (or do a Google Search on "Access folder browse VBA".

 

[Deirdre]

Joe4 - Many many many thanks! Your solution was right on and I am very appreciative of your time and patience.
You have saved me a few hours of work each month!
You are a true gem!
Thank you Thank you Thank you!

 

[Joe4]

You are welcome!
Glad I was able to help!