Combine Csv Files Into One Excel Workbook

Add a new workbook, all the data from multiple csv files will be imported in this workbook; Add the column header for 2 columns in cell A1 and B1; Loop through each csv file in source data folder; Copy the range from opened csv file, we have taken the range A2:B10 for demonstration purpose; Paste the copied data in last blank row in a newly. In this tutorial, I will teach you how to write a VBA script to merge all the CSV files in a folder into a single Excel file. Exercise file used in the video download. Option Explicit. Dim wsMerge As Worksheet. Dim RowInsert As Long. Sub MergeFiles. When it comes to merge or combine multiple worksheets / workbooks or csv files into one workbook, most of us will think about copying and pasting the worksheets into one workbook. But if there are lots of worksheets need to be merged by such way, it will waste much time. If you want to combine a large number of CSV files into one Excel file, it’s more efficient to consolidate from a folder. Sheetgo lets you consolidate up to 80 files in one. It’s a time-saving option as there’s no need to select each source file individually. On top of that, it automates another stage of your workflow.

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.

Other VBA File resources

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.

Open CMD command line within folder

Click on the filepath of the Windows Explorer window and type cmd and hit ENTER.

Merge the files using COPY command

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


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

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.

Merge csv, txt files example

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

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.

Merge csv, txt files in folder example

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

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.

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 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.

Related posts:
How to COMBINE Multiple Excel WORKBOOKS into One Workbook

Combine multiple excel workbooks into one workbook

We might need to combine multiple excel workbooks into one workbook in order to analyse or present the information better. Though this can be done manually, by moving or copying all the worksheets from different workbooks into one workbook, but it would be time consuming and prone to errors. Moreover if there are a large number of workbooks/worksheets, then the problem is even bigger.

However, we have a solution to this and it would not take more than a few seconds to get all workbooks merged into one. Here is the VBA code that can help us achieve this. Even those who are not well versed with VBA can easily get this done by simply following the steps on how to use this code:

VBA Code for XLS Files

VBA Code for XLSX Files

How to Use this Code?

Below are the steps to use this code and there’s also a video at the end:

  • (1) All the Excel workbooks that you want to combine should be placed in a folder – For the purpose of this tutorial, we have created a folder named 'Excel Workbooks' and have three files in it.
  • (2) Open a new Excel workbook.
  • (3) Press ALT + F11 (or go to Developer –> Visual Basic). This will open the Visual Basic Editor.
  • (4) In the VB Editor menu go to Insert –> Module. This will insert a module for the workbook with a blank code window.
  • (5) Copy and paste the above code into the code window.
  • (6) In the code, you need to change the following line of code:
  • (7) In this line, insert the location of the folder in which you have placed the files that you want to combine, between quotes. In the code used above, the folder is in 'Documents'. Get the path by visiting the folder and clicking on the address bar dropdown. We insert C:UsersNiteshDocumentsExcel Workbooks between the quotes. (Note that a back slash has been added at the end of the folder path)
  • (8) Place the cursor anywhere in the code and click on the green play button in the Toolbar options (or press the F5 key).

Combine Csv Files Into One Excel Workbook

  • (9) This will activate the code and all worksheets from all the Excel workbooks in the folder would get consolidated into a single workbook.
  • (10) Save the Master Workbook, and you're done.

How does this Code Work?

The code uses the DIR function to get the file names from the specified folder.

The following line assigns the first excel file name to the variable 'Filename'.

Filename = Dir(Path & '*.xls*')

Then the Do While loop is used to check whether all the files have been covered.

Within the Do While loop, For Each loop is used to copy all the worksheets to the workbook in which we are running the code.

At the end of the Do Loop, following line of code is used: Filename = Dir(). It assigns the next Excel file name to the Filename variable and the loop starts again.

When all the files are covered, DIR function returns an empty string, which is when the loop ends.

Here is an explanation of the DIR function in the MSDN library:

Merge Multiple Csv Files Into One Excel Workbook Macro

Dir returns the first file name that matches pathname. To get any additional file names that match pathname, call Dir again with no arguments. When no more file names match, Dir returns a zero-length string (').

Combine Two Workbooks Excel

We hope you found this interesting. Your comments/feedback would be appreciated.

Video