Tag Archives: merge

merge csv files

Merge CSV files or TXT files in a folder – using Excel or CMD

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

Often we face the challenge of having to merge csv files or txt files in a folder, into a single file. Excel is the obvious tool for such tasks and today I will show a couple of easy ways for merging multiple files, in a single or even a whole structure of folders, into a single CSV or text file. To merge csv files or other text files it is often best to use Visual Basic for Applications in Excel.

Let’s start with the simplest approach using Windows Command line without having to use Excel.

Merge CSV files using Windows CMD

This approach uses the Windows Command line Copy command.

Open the folder which should contain your CSV or TXT files

Open in Windows Explorer the folder containing CSV or TXT files to be merged. These should be without headers or only the first file should be with headers.
merge CSV files Windows CMD

Open CMD command line within folder

Click on the filepath of the Windows Explorer window and type cmd and hit ENTER.
merge CSV files Windows CMD

Merge the files using COPY command

The CMD Windows command line Window should open. Type the following command and hit ENTER to merge files

copy *.csv merge.csv

merge CSV files Windows CMD
The result will be the newly created merge.csv file with merged data across all CSV files within the directory.

Simply replace *.csv with *.txt to merge text files instead of CSV files

Merge list of csv, txt files

The previous method was very simple and didn’t require the use of Excel or MS Office. The below and the following approaches will provided you with more flexibility when merging files. If you don’t know how to use Macros in Excel read my Tutorial first.

Assuming you want to merge a list of files in a String Array you can use the procedure below. It will merge all provided csv or text files into a single new text file.

Sub MergeFiles(fileNames() As String, newFileName As String, Optional headers As Boolean = True, Optional addNewLine As Boolean = False)
    Dim fileName As Variant, textData As String, fileNo As Integer, result As String, firstHeader As Boolean
    firstHeader = True
    For Each fileName In fileNames
        fileNo = FreeFile
        Open fileName For Input As #fileNo
        textData = Input$(LOF(fileNo), fileNo)
        Close #fileNo
        If headers Then
            result = result & IIf(addNewLine, vbNewLine, "") & IIf(firstHeader, textData, Right(textData, Len(textData) - InStr(textData, vbNewLine)))
            firstHeader = False
        Else
            result = result & IIf(addNewLine, vbNewLine, "") & textData
        End If
    Next fileName
    fileNo = FreeFile
    Open newFileName For Output As #fileNo
    Print #fileNo, result
    Close #fileNo
End Sub

Merge csv, txt files example

See an example below of how to use the MergeFiles procedure:

Dim fileNames(0 To 1) As String
fileNames(0) = "C:\somefolder\test.csv"
fileNames(1) = "C:\somefolder\test1.csv"
    
MergeFiles fileNames, "C:\Merged.csv", True, False

MergeFiles Parameters

fileNames()
Array of Strings representing full file paths to files that are to be merged

newFileName
The name of the new merged file that is to be created

headers
Optional. True by default. This is meant for CSV TXT files (HDR). If True assumes that all files have headers (first row with columns). Only first header will be merged into the new file (newFileName)

addNewLine
Optional. False by default. If True a new line (vbNewLine) character will be added between each merged file

Merge csv, txt files within specified folder

Another case is when you want to merge all csv files within a single folder. This procedure is similar to the previous one with the exception that it runs through all files within a single directory (excluding subdirectories – for that scroll to next procedure). You can also use wildcards such as “*.csv” to be sure that only csv files are merged a not other files – read my post on the VBA Dir function to learn more.

Sub MergeFilesInFolder(folderName As String, newFileName As String, Optional headers As Boolean = True, Optional addNewLine As Boolean = False)
    Dim fileName As Variant, textData As String, fileNo As Integer, result As String, firstHeader As Boolean
    firstHeader = True
    fileName = Dir(folderName)
    Do Until fileName = ""
        fileNo = FreeFile
        Open (Left(folderName, InStrRev(folderName, "\")) & fileName) For Input As #fileNo
        textData = Input$(LOF(fileNo), fileNo)
        Close #fileNo
        If headers Then
            result = result & IIf(addNewLine, vbNewLine, "") & IIf(firstHeader, textData, Right(textData, Len(textData) - InStr(textData, vbNewLine)))
            firstHeader = False
        Else
            result = result & IIf(addNewLine, vbNewLine, "") & textData
        End If
        fileName = Dir
    Loop
    fileNo = FreeFile
    Open newFileName For Output As #fileNo
    Print #fileNo, result
    Close #fileNo
End Sub

Merge csv, txt files in folder example

See an example below of how to use the MergeFilesInFolder procedure:

MergeFilesInFolder "C:\somefolder\*.csv", "C:\MergedFolder.csv", True, False

MergeFilesInFolder Parameters

folderName
A folder including all files to be merged. Wildcards are permitted if supported by the VBA Dir function

newFileName
The name of the new merged file that is to be created

headers
Optional. True by default. This is meant for csv files (HDR). If True assumes that all files have headers (first row with columns). Only first header will be merged into the new file (newFileName)

addNewLine
Optional. False by default. If True a new line (vbNewLine) character will be added between each merged file

Merge csv, txt files within all subfolders

The most complex case is when you want to merge files not only within a certain directory but also within all subdirectories. This will equally work for a scenario when there are no subfolders.

Sub MergeFilesInSubFolders(folderName As String, pattern As String, newFileName As String, Optional headers As Boolean = True, Optional addNewLine As Boolean = False)
    Dim fileName As Variant, folder As Variant, textData As String, fileNo As Integer, result As String, firstHeader As Boolean
    firstHeader = True
    Dim col As Collection
    Set col = New Collection
    col.Add folderName
    TraversePath folderName, col
    For Each folder In col
        fileName = Dir(folder & pattern)
        Do Until fileName = ""
            fileNo = FreeFile
            Open (Left(folder, InStrRev(folder, "\")) & fileName) For Input As #fileNo
            textData = Input$(LOF(fileNo), fileNo)
            Close #fileNo
            If headers Then
                result = result & IIf(addNewLine, vbNewLine, "") & IIf(firstHeader, textData, Right(textData, Len(textData) - InStr(textData, vbNewLine)))
                firstHeader = False
            Else
                result = result & IIf(addNewLine, vbNewLine, "") & textData
            End If
            fileName = Dir
        Loop
    Next folder
    fileNo = FreeFile
    Open newFileName For Output As #fileNo
    Print #fileNo, result
    Close #fileNo
End Sub

Function TraversePath(path As Variant, allDirCollection As Collection)
    Dim currentPath As String, directory As Variant
    Dim dirCollection As Collection
    Set dirCollection = New Collection
     
    currentPath = Dir(path, vbDirectory)
    'Explore current directory
    Do Until currentPath = vbNullString
        If Left(currentPath, 1) <> "." And Left(currentPath, 2) <> ".." And _
            (GetAttr(path & currentPath) And vbDirectory) = vbDirectory Then
            dirCollection.Add path & currentPath & "\"
            allDirCollection.Add path & currentPath & "\"
        End If
        currentPath = Dir()
    Loop
     
    'Explore subsequent directories
    For Each directory In dirCollection
        TraversePath directory, allDirCollection
    Next directory
End Function
Read this post to learn more on using the VBA Dir function to traverse directories and subdirectories

Merge csv, txt files within subfolders example

See an example below of how to use the MergeFilesInSubFoldersprocedure:

MergeFilesInSubFolders "C:\somefolder\", "C:\MergedSubFolders.csv", True, False

MergeFilesInSubFolders Parameters

folderName
A folder with or without subfolders including all files to be merged. Use wildcards with pattern parameter

pattern
If needed a pattern using wildcards permitted by the VBA Dir function

newFileName
The name of the new merged file that is to be created

headers
Optional. True by default. This is meant for csv files (HDR). If True assumes that all files have headers (first row with columns). Only first header will be merged into the new file (newFileName)

addNewLine
Optional. False by default. If True a new line (vbNewLine) character will be added between each merged file

Merge CSV files – filter records

Sometimes we want to download just a subset of records in our CSV files. One way is uploading the data and then filtering it in Excel. But why not do it in one go? See my SQL AddIn or my read CSV file using SQL example in this post here.

merge worksheets

Merging worksheets / tables in Excel

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 4.75 out of 5)
Loading...

Often we need to merge worksheets / workbooks containing lots of data. Imagine receiving periodical daily reports and wanting to quickly consolidate them to generate a weekly or monthly report… seems like a lot of work. Merging worksheets does not necessarily need to be hard… as long as you read through today’s post.

Merging worksheets with VBA

To exemplify the issue let’s consider a Workbook consisting of 2 worksheets (below named Sheet1 and Sheet2) with identical columns.

consolidate worksheets
Consolidating worksheets: Example data

What we want to do is merge these 2 worksheets into one consolidated worksheet. We may of course want to do this in multiple ways e.g. by:

  • Simply appending the worksheets to each other
  • Merging the data sets while removing duplicates

Here is the example result we want to achieve (Sheet2 appended to Sheet1 on a third separate Worksheet):

merging worksheets
Merging worksheets: Result

In today’s Tip of the Day I will exemplify how to merge worksheets / tables (any number) with an option of eliminating duplicates.

Merging worksheets with VBA

Let’s first consider a simple piece of VBA code. The procedure below can handle any number of Worksheets – including Worksheets from external Workbooks as long as you have the Workbook open and provide it within the array of Worksheets passed to the procedure.

VBA Code

Sub Merge(ws() As Worksheet, destWs As Worksheet, headerInFirstRow As Boolean, removeDuplicates As Boolean)
    'Clear destination worksheet
    destWs.UsedRange.EntireRow.Delete
    Dim pasteRange As Range, header As Range, firstFreeRow As Range, w As Variant, copyRange As Range
    'Paste header
    If headerInFirstRow Then
        Set header = ws(0).UsedRange.Cells(1).EntireRow:
        header.Copy destWs.Cells(1).EntireRow
    End If
    Set firstFreeRow = destWs.UsedRange.Rows(destWs.UsedRange.Rows.Count).Offset(1).EntireRow
    'Paste worksheets
    For Each w In ws
        Set copyRange = w.UsedRange.Rows("" & _
            IIf(headerInFirstRow, 2, 1) & ":" & w.UsedRange.Rows.Count)
        copyRange.Copy firstFreeRow.Cells(1, 1)
        Set copyRange = Nothing
        Set firstFreeRow = destWs.UsedRange.Rows(destWs.UsedRange.Rows.Count).Offset(1).EntireRow
    Next w
    'Remove duplicates
    If removeDuplicates Then
        Dim colArr As Variant, col As Long: ReDim colArr(0 To destWs.UsedRange.Columns.Count - 1)
        For col = 1 To destWs.UsedRange.Columns.Count
            colArr(col - 1) = col
        Next col
        destWs.UsedRange.removeDuplicates Columns:=(colArr), header:=IIf(headerInFirstRow, xlYes, xlNo)
    End If
    'Clean
    Set firstFreeRow = Nothing: Set w = Nothing: Set header = Nothing
End Sub

  

A now a simple example of this procedure being used assuming the example data I referenced above. Let’s assume data on is on worksheets Sheet1 and Sheet2, the destination Worksheet will be Sheet3.

Sub TestMerge()
    Dim ws(0 To 1) As Worksheet
    Set ws(0) = Sheet1
    Set ws(1) = Sheet2
    Merge ws, Sheet3, True, False
End Sub

Notice that the Merge procedure accepts the following parameters:

  • ws – an array of Worksheets
  • destWs – the destination Worksheet
  • headerInFirstRow – if true assumes that the header is in the first row of each source Worksheet
  • removeDuplicates – if true remove all duplicates after merging the Worksheets

Simple enough right? Not too long. If you want to optimize it for best performance read this.

Merging worksheets with MS Query (SQL)

Now let’s consider a second approach – using MS Query (SQL). This is my personal favorite as MS Query SQL code is short, does not require VBA and the destination Worksheet can be refresh by simply right clicking on the table and hitting refresh.

To create a MS Query feel free to use my free Excel SQL AddIn or follow the steps below:

merge worksheets excel
SQL AddIn: Merging worksheets

Follow the instructions below if you don’t have my free Excel SQL AddIn:

Open the From Microsoft Query Wizard

Data ribbon and then selecting From Other Sources and From Microsoft Query -> till the end be sure to hit View Data or Edit Query in Microsoft Query to be able to provide your own SQL query.

Proceed according to Wizard to edit SQL Query

As we want to merge several Worksheets within our current Excel Workbook we need to select Excel Files as the designated data source for our MS Query:

Select From Excel Files data source
Select From Excel Files data source

Next we need to select our Current Excel Workbook from the File Dialog:
Select your current Excel Workbook
Select your current Excel Workbook

Select the first Excel Worksheet you want to merge and click the right arrow > to drag it to the Columns in your Query section.
Select any Worksheet you want to merge to proceed
Select any Worksheet you want to merge to proceed

Next instead of Returning the Data hit the View data or edit Query in Microsoft Query radio button and proceed:
Proceed to edit the MS Query
Proceed to edit the MS Query

A new Window should pop-up. Look for the SQL button to modify the existing MS Query:
Hit SQL to edit MS Query
Hit SQL to edit MS Query

Modify the SQL Query to merge Worksheets

A new SQL Window should pop-up. You can modify any number of Worksheets using this approach. Below I am assuming I want to merge two Worksheets – Sheet1 and Sheet2. The MS Query need to therefore look like this:

SELECT * FROM [Sheet1$] UNION ALL SELECT * FROM [Sheet2$]

Simply modify the existing MS Query in the SQL Window and hit OK.

What if you need to merge 3 Worksheets not just 1? Simply append an additional UNION ALL like this:

SELECT * FROM [Sheet1$] UNION ALL SELECT * FROM [Sheet2$] UNION ALL SELECT * FROM [Sheet3$]

For additional Worksheets repeat this pattern

Return Data to selected Worksheet

Return Data to Excel Workbook
Return Data to Excel Workbook
Now the pleasant part – loading the Query data. Look for the Return Data button, select a cell where the MS Query is supposed to load and hit OK.

Comment on using the MS Query approach

Simple and quick – this approach is more efficient than any VBA code we may develop as it is handled by the OLEDB driver. MS Query (SQL) is also more efficient in handling large chunks of data as compared to other approaches.

Merging worksheets with PowerQuery

If you are not familiar with PowerQuery feel free to download it from Microsoft here. PowerQuery features a lot of neat data-crunching / BI features. Let’s see how can we leverage PowerQuery to do our job.

Create tables from each source Worksheet

First what we need to do is link our source tables to PowerQuery so we can create the output query.
consolidate2
Select the whole range of each source worksheet (CTRL + A)
and select the From Table option from the PowerQuery ribbon. For this example I have named Sheet1 as Table1 and similarly with Sheet2.

Merge the Worksheets

So now that we have our Tables configured in PowerQuery we can append the 2 (or more). Hit the Append button from the Combine group in the PowerQuery ribbon.

PowerQuery - Combine
PowerQuery – Combine

Next select Table1 from the first drop-down and Table2 from the second drop-down:

PowerQuery - Append
PowerQuery – Append

and hit OK

Note: When wanting to append multiple (>2) worksheets simply repeat this step. The first Table will need to be the Appended one from the remaining tables

That’s it! Similarly as with the MS Query example you can always easily refresh your query by right-clicking and hitting Refresh!

Summary

I hope you found these methods useful. On one last note I would like to share my thoughts on how these methods compare.

Personally I prefer to use MS Query (SQL) via my SQL AddIn. This sentiment may of course be partially as I used to work a lot with databases and am used to coding SQL. I know for most Excel users out there this is not the case. In such cases and if you only have a couple of Worksheets you need to merge on a regular basis I do suggest downloading the PowerQuery AddIn. It’s easy to use and also allows you to refresh your query easily. In all other scenarios feel free to use the VBA approach and simply customize your code to work best for you!

Let me know if you found this useful! And be sure to subscribe to more Tips on my Twitter or Facebook page!