# Bulk combine csv files and create column with file name

#### isthisthewaywego

##### New Member
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

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
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
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)
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:

#### isthisthewaywego

##### New Member
That's fantastic, thanks so much!

#### eskays30

##### New Member
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)
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.

Replies
1
Views
636
Replies
1
Views
319
Replies
8
Views
438
Replies
3
Views
783
Replies
2
Views
604

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.

### Which adblocker are you using?

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

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