VBA Combine all csv files in folder

[John BX]

Hi,

I have a folder (inputfolder) which will contain multiple .csv files.

• All the .csv files have headers.
• Some .csv files may have fewer headers
• The headers may not always be in the same order

My goal is to have the 1 master .csv file which will be updated with the .csv files in the inputfolder.
I will create the master file to have all the headers that the multiple .csv files may have.

I found some code which merges any .csv files found in a given folder, but I cant figure out how to get it to always use the same master file, and also handle the problem of the different headers.

Thanks in advance

Option Explicit

Sub ImportCSV()


    Dim strSourcePath As String
    Dim strDestPath As String
    Dim strFile As String
    Dim strData As String
    Dim x As Variant
    Dim Cnt As Long
    Dim r As Long
    Dim c As Long
    

    Application.ScreenUpdating = False
    
    'Change the path to the source folder accordingly
    strSourcePath = "c:\input\"
        
    If Right(strSourcePath, 1) <> "\" Then strSourcePath = strSourcePath & "\"
    
    'Change the path to the destination folder accordingly
    strDestPath = "c:\output\"
    
        If Right(strDestPath, 1) <> "\" Then strDestPath = strDestPath & "\"
    
    strFile = Dir(strSourcePath & "*.csv")
    
    Do While Len(strFile) > 0
        Cnt = Cnt + 1
        If Cnt = 1 Then
            r = 1
        Else
            r = Cells(Rows.Count, "A").End(xlUp).Row + 1
        End If
        Open strSourcePath & strFile For Input As #1
            If Cnt > 1 Then
                Line Input #1, strData
            End If
            Do Until EOF(1)
                Line Input #1, strData
                x = Split(strData, ",")
                For c = 0 To UBound(x)
                    Cells(r, c + 1).Value = Trim(x(c))
                Next c
                r = r + 1
            Loop
        Close #1
        Name strSourcePath & strFile As strDestPath & strFile
        strFile = Dir
    Loop
    
    Application.ScreenUpdating = True
    
    If Cnt = 0 Then _
        MsgBox "No CSV files were found...", vbExclamation
    
End Sub

 

[John BX]

I forgot to mention, I am using Excel 2013