Excel Macro to Combine .csv Files
March 8, 2017
I have posted in the past on Excel and Access macros which can be used to merge multiple Excel files together. Â Today I worked with a macro posted to the site of Jerry Beaucaire, which can be used to merge multiple .csv files together. Â Â The macro is posted below. Â This particular macro will add the name of each source file to the first column on the spreadsheet in which the .csv files are merged together.Â
Â
Â

Â
Â
In Excel, just follow the standard practice of pressing ALT + Â F11 and then inserting the VBA code in a new module. Â You need to specify the folder containing your .csv files in the macro where it reads, fPath =, and then a line above that enter the name of the worksheet on which you want the data added - in place of 'MasterCSV'. Â Â
Â

Â
Â
I used the macro today on a set of more than 500 .csv files that totaled over 70 MB, and it worked without a hitch.  It took no more than 10 minutes to process these.  You do however want to avoid using other applications on your PC while the macro runs. Â
Â
Â

Â
Â
Option Explicit
Sub ImportCSVsWithReference()
'Author: Â Â Jerry Beaucaire
'Date: Â Â Â 10/16/2010
'Summary: Â Import all CSV files from a folder into a single sheet
' Â Â Â Â Â adding a field in column A listing the CSV filenames
Dim wbCSV Â As Workbook
Dim wsMstr  As Worksheet:  Set wsMstr = ThisWorkbook.Sheets("MasterCSV")
Dim fPath  As String:    fPath = "C:\2010\Import\"   'path to CSV files, include the final \
Dim fCSV Â Â As String
If MsgBox("Clear the existing MasterCSV sheet before importing?", vbYesNo, "Clear?") _
  = vbYes Then wsMstr.UsedRange.Clear
Application.ScreenUpdating = False  'speed up macro
fCSV = Dir(fPath & "*.csv") Â Â Â Â 'start the CSV file listing
  Do While Len(fCSV) > 0
   'open a CSV file
    Set wbCSV = Workbooks.Open(fPath & fCSV)
   'insert col A and add CSV name
    Columns(1).Insert xlShiftToRight
    Columns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name
   'copy date into master sheet and close source file
    ActiveSheet.UsedRange.Copy wsMstr.Range("A" & Rows.Count).End(xlUp).Offset(1)
    wbCSV.Close False
   'ready next CSV
    fCSV = Dir
  Loop
Â
Application.ScreenUpdating = True
End Sub