Combine Excel File Into One

 admin
  1. Combine Excel Files Into One Spreadsheet
  2. Combine Excel Files Into One Workbook Python
  3. Combine Excel File Into One

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.

  • Step 1: Press the ALT + F11 keys at same time to open the Microsoft Visual Basic for Applications window. Step 2: Click InsertModule, then paste the under codes into the newly opened module. Sub Combine Dim Sun As. Step 3: Press F5 or click the run icon in the toolbar. Then you would see.
  • Merge Excel Files: How to Combine Workbooks into One File; Return Blank Cells Instead of Zeroes in Excel Formulas; Thousands or Millions in Excel: How to Change the Number Unit; Wrong Calculations - Why Does Excel Show a Wrong Result? Table of Contents in Excel: 4 Ways to Create a Directory; INDIRECT: How to Use Text as a Cell Reference in Excel.
Other VBA File resources

Video tutorial to merge multiple CSV files into 1 on Macbook. Currently, on a Macbook or MacOS, there is only a way to merge multiple CSV files into one, but XLSX files are not. To merge CSV files, follow the instructions below: Step 1: You put all CSV files into a folder, then click the wheel button and choose Copy “folder name” as path name.

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

Combine

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.

Combine Excel Files Into One Spreadsheet

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.

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

Combine Excel Files Into One Workbook Python

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

Combine Excel File Into One

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.

Combine excel files into one worksheet
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.

Combine Excel File Into One

Related posts: