Excel Macro to Combine .csv Files

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