Excel Macro to Combine .csv Files

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

Please reload

Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with Wix.com