Bulk combine csv files and create column with file name

[isthisthewaywego]

Hi,


I have about 4000 csv files, all with about 10 lines of data in them. Each file has the same headers, and each has its own individual file name. I would like to combine these files into one big csv file, but adding a different column which specifies, next to each line of data, the name of the file that the data was originally sourced from.


So if example1.csv had the following contents:


ProductCode,Price,Quantity
WidgetA,10,2


And example2.csv had the following contents:


ProductCode,Price,Quantity
WidgetA,8,4


I would like the combined file to be as follows:


ProductCode,Price,Quantity,<wbr>OriginalFile
WidgetA,10,2,example1.csv
WidgetB,8,4,example2.csv


Anyone have any ideas as to how I might go about this?


Cheers!
Dan

 

[isthisthewaywego]

Actually - just spotted an error there. Example2.csv would have the following contents (to make it consistent with the desired output file result)

ProductCode,Price,Quantity
WidgetB,8,4

 

[John_w]

Try this. You need to change the input folder and output file name next to the comments near the top of the code.

Public Sub Combine_CSV_Files()

    Dim CSVfolder As String, outputCSVfile As String
    Dim FSO As Object, FSOfolder As Object, FSOfile As Object, FSOstream As Object
    Dim CSVdata() As String
    Dim CSVrows As Variant
    Dim startRow As Long, r As Long, n As Long, rows As Long
    
    CSVfolder = "C:\path\to\csv_folder\"                'Folder containing the CSV files to be combined
    outputCSVfile = "C:\path\to\combined.csv"           'File name of combined CSV file to be created
    
    startRow = 1
    rows = 0
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FSOfolder = FSO.GetFolder(CSVfolder)
    
    'Read rows from each CSV file into array
    
    For Each FSOfile In FSOfolder.Files
        Set FSOstream = FSOfile.OpenAsTextStream(1)
        CSVrows = Split(FSOstream.ReadAll, vbCrLf)
        FSOstream.Close
        n = UBound(CSVrows)
        If CSVrows(UBound(CSVrows)) = "" Then n = n - 1     'ignore trailing blank row if present in CSV file
        ReDim Preserve CSVdata(rows + n - startRow + 1)
        For r = startRow - 1 To n
            If rows = 0 Then
                CSVdata(rows) = CSVrows(r) & ",OriginalFile"
            Else
                CSVdata(rows) = CSVrows(r) & "," & FSOfile.Name
            End If
            rows = rows + 1
        Next
        startRow = 2
    Next
    
    'Create combined CSV file
    
    Set FSOstream = FSO.CreateTextFile(outputCSVfile)
    FSOstream.Write Join(CSVdata, vbCrLf)
    FSOstream.Close
    
End Sub

 

[isthisthewaywego]

That's fantastic, thanks so much!

 

[eskays30]

Try this. You need to change the input folder and output file name next to the comments near the top of the code.

Public Sub Combine_CSV_Files()

    Dim CSVfolder As String, outputCSVfile As String
    Dim FSO As Object, FSOfolder As Object, FSOfile As Object, FSOstream As Object
    Dim CSVdata() As String
    Dim CSVrows As Variant
    Dim startRow As Long, r As Long, n As Long, rows As Long
    
    CSVfolder = "C:\path\to\csv_folder\"                'Folder containing the CSV files to be combined
    outputCSVfile = "C:\path\to\combined.csv"           'File name of combined CSV file to be created
    
    startRow = 1
    rows = 0
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FSOfolder = FSO.GetFolder(CSVfolder)
    
    'Read rows from each CSV file into array
    
    For Each FSOfile In FSOfolder.Files
        Set FSOstream = FSOfile.OpenAsTextStream(1)
        CSVrows = Split(FSOstream.ReadAll, vbCrLf)
        FSOstream.Close
        n = UBound(CSVrows)
        If CSVrows(UBound(CSVrows)) = "" Then n = n - 1     'ignore trailing blank row if present in CSV file
        ReDim Preserve CSVdata(rows + n - startRow + 1)
        For r = startRow - 1 To n
            If rows = 0 Then
                CSVdata(rows) = CSVrows(r) & ",OriginalFile"
            Else
                CSVdata(rows) = CSVrows(r) & "," & FSOfile.Name
            End If
            rows = rows + 1
        Next
        startRow = 2
    Next
    
    'Create combined CSV file
    
    Set FSOstream = FSO.CreateTextFile(outputCSVfile)
    FSOstream.Write Join(CSVdata, vbCrLf)
    FSOstream.Close
    
End Sub

I have tried this code but didn't worked for me.
My requirement
i have 100 csv files with similer content in every file
like Columns Article id, author, headlines, content, media type, url, date...

i want to merge all files into one single file but at the same time i want to create a column with filename for each csv.

I have tried the above code but i gave me only one file out of 100 data without filename column. I can use macro and do some small changes (file path etc) but not expert in vba. please guide me.

 

[John_w]

I tested the code and it works for me. Add the following line after the n = UBound(CSVrows) line to verify that the code is reading the files correctly:

        MsgBox FSOfile.Path & vbCrLf & n + 1 & " rows"

Alternatively, try the code at http://www.mrexcel.com/forum/excel-...t-column-b;-file-name-column.html#post3944033