Bulk combine csv files and create column with file name

isthisthewaywego

New Member
Joined
Apr 22, 2014
Messages
3
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

isthisthewaywego

New Member
Joined
Apr 22, 2014
Messages
3
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,903
Try this. You need to change the input folder and output file name next to the comments near the top of the code.
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
 
Last edited:

eskays30

New Member
Joined
Jun 1, 2016
Messages
1
Try this. You need to change the input folder and output file name next to the comments near the top of the code.
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.
 

Forum statistics

Threads
1,136,864
Messages
5,678,214
Members
419,751
Latest member
TryingtoLearnVBA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top