Import from Multiple CSV files

[Dave177]

Hello

I don't think this is possible but I will ask it anyway as it would save a lot of time if it was...

Every month I receive about 100 csv files and would like to import these into Access. They all have the same fields etc but are in individual files.

Can you use something like:
DoCmd.TransferText acImportDelim, <specification>, <table>, <file with full path>, False
to import many files together if they were placed in the same folder?

Thanks for any advice.

Dave

 

[Golom]

Not as you have done it but you can run a routine to combine the files before you import them with TransferText.

'---------------------------------------------------------------------------------------
' Procedure : CombineFiles
' Purpose   : Create a new file that combines the contents of the selected files.
'---------------------------------------------------------------------------------------
'
Public Sub CombineFiles(SourceFiles() As Variant, _
                        ByVal DestPath As String, _
                        ByVal DestFile As String)

    Dim FSO                         As New FileSystemObject
    Dim Tst_out                     As TextStream
    Dim NewFile                     As String
    Dim n                           As Integer
    Dim Ans                         As Integer

    NewFile = Replace(DestPath & "\" & Trim$(DestFile), "\\", "\")

    Ans = vbYes
    If FSO.FileExists(NewFile) Then
        Ans = MsgBox("The file '" & NewFile & "' already exists." & vbCrLf & vbCrLf & _
                     "Do you want to replace it?", vbQuestion + vbYesNo, "Replace File?")
    End If

    If Ans = vbYes Then
        Set Tst_out = FSO.CreateTextFile(NewFile)
        For n = LBound(SourceFiles) To UBound(SourceFiles)
            If Len(SourceFiles(n)) > 0 Then
                If SourceFiles(n) = NewFile Then
                    MsgBox "Source and Destination cannot be the same file." & vbCrLf & _
                           "File '" & NewFile & "' bypassed.", vbExclamation, "Invalid Source"
                Else
                    Tst_out.Write FSO.OpenTextFile(SourceFiles(n)).ReadAll
                End If
            End If
        Next

        Tst_out.Close
    End If

    Set Tst_out = Nothing
    Set FSO = Nothing

End Sub

 

[PHV]

You may consider a VBA loop with the Dir function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dave177]

Thanks very much for your help - I haven't had time to try it yet but will do as soon as I can

Dave