Have a bunch of CSV files in a folder and want to merge or combine them to one big file? Follow these simple instructions to combine multiple CSV files in to one spreadssheet using Microsoft Excel (2016 or above).
What you need?
- A folder with CSV files (click here to download sample files, if you need some)
- Microsoft Excel (2016 or above version)
Method 1: Merge CSV Files using Power Query (Recommended)
This is by far the easiest and quickest way to combine data from CSV files in a folder using Excel.
Follow these steps:
- Close any opened CSV files: Before proceeding, close any of the opened CSV files. Make a note of the folder path too.
- Open Excel and make a new file: This will be our merged CSV data file. Here, we will setup Power Query to combine all the files.
- Go to Data Ribbon > Get Data and click on From Folder
Paste the folder path or navigate to the folder and click “select”.
- For simple CSV files: If your CSVs are already clean and you just want to merge them, select the Combine > Combine & Load option.
- For complex CSVs or if you need to clean-up data before merge: Select the Combine > Combine & Transform Data option. This will open up “Power Query Editor” so you can clean up data or apply “transformations”.
- Click “OK” in the next screen: This next screen shows a sample of your data (usually the first file) so you can confirm to Excel how your data looks. If your delimiter is not comma (for example, you have TSV files instead of CSV), you can also tell Excel about that using this screen.
- If you selected “Combine & Load” option: Your merge is done! The combined data from your CSV files is now loaded into Excel. This is how it will look (see below). You can use the “Source name” column to figure out which file each row came from.
For “Advanced” Merge Scenarios – Combine & Transform Data
If your CSV files are not so simple or you want to further clean-up data after merging, you can follow these steps.
Select the “Combine & Transform Data” option in after Step 3 (ie once you point the folder). This will take you to Power Query editor (after you confirm the file details, as shown in step 6 above).
Once you are in Power Query Editor screen, you can apply any data clean-up and transformation steps on your data easily. I will share a few examples below. But refer to my Power Query tutorial page or video for detailed information on how to use Power Query for data cleaning and transformations.
Example 1: Removing the “Total” column from merged CSV files
Let’s say you don’t want the “total” column from these merged budget files. In the Power Query editor, right click on the “total” column and select “remove”. This will remove the total budget column. Don’t worry, it is not going to remove data from original CSV files. But when you merge the data, you won’t just see the “total” column.
Example 2: Reshaping the 12 columns to month & value column structure
While the 12 monthly column structure works best for gathering budget data, it may not be ideal for data analysis. So let’s reshape our merged data to a format like this:
- File name
- Budget Category
- Month
- Budget value
Essentially, each row of the data in original CSV file becomes 12 rows in merged file. This process is caleld “unpivoting”. It looks like this:
To unpivot data in Power Query Editor:
- Select the file name & cost category columns (and any other columns you want to retain).
- Tip: You can hold Shift or CTRL to multi-select columns in Power Query Editor.
- Right click on the selected columns and select “Unpivot other columns” option.
This will replace the 12 monthly columns with a new “Attribute” and “Value” columns. These are nothing but our Month and Budget columns!
Renaming the columns: You can double click on the column header and rename it to Month and Budget.
Load combined CSV data to Excel:
When you finish the data clean up and transformations you want to do, go to the Home ribbon in Power Query editor and click on “Close & Load” to bring the finalized data to Excel.
Why this method is the best?
I have been using (and advocating) Power Query for more than 10 years. I can’t tell you how much time and effort this little trick has saved me. Here are my top reasons for why Power Query is the best way to merge CSV files.
- Fully Dynamic: You don’t need to worry about changing files or growing (or even shrinking) data. Once you properly set up the Power Query connection, your data will be merged automatically even if there are 1000s of files.
- Automated: One of the biggest challenges with data merges like this is that your raw data files change often. With Power Query, updating the “merged” dataset is really simple. Open the merged file, right click anywhere on the merged data table and select “Refresh” to automatically update the merged CSV data.
- Works even if the columns are out of order*: This method works just as perfectly even if your CSV files have columns in jumbled order, as long as the column headings are same across files. That means if file 1 has “Cost category” as column 1 and file 3 has “Cost category” as column 14, the merge still works, as the column heading is matching in both cases. I talk about how to deal with more complex situations of mismatched headers in this video.
Other ways to combine or merge CSV files with Excel
We can also use below techniques to merge CSV files with Excel (I prefer Power Query btw).
- Using VBA Macros to combine CSV files: Excel’s own coding language – VBA offers a powerful and proven way to combine multiple files (CSV, Text or even other Excel files) and get merged data in one place. This is an advanced method and not really recommended for beginners. Refer to this article for a detailed step-by-step instruction on how to combine data with Excel VBA
- Manual Copy Pasting: For something quick and dirty, you can also manually open the CSV files and copy paste the data into master Excel file. This is an error-prone and labor intensive process and should only be used in one-off cases.
- Command Line Utilities: As CSVs are just text files, you can also use a simple command line utility to combine multiple CSVs to one file. This has the disadvantage of repeating headers and not working when the headers don’t match up or columns don’t align. Here is the command for Windows. This combines the CSV files in the sub-folder “merge csv” to a new file named “combined_file.csv”.
copy /b "merge csv"\*.csv combined_file.csv
- Using online tools: Plenty of online tools offer CSV merge functionality. You can upload your files on these websites and they will combine them for you. I suggest checking the privacy policies of these websites and using them only if you can’t apply Power Query or VBA or manual methods. Here are a few that offer this service: MergeCSV, CSV Combiner
Best Practices when Merging CSV Files (with PQ in Excel):
- Columns should match (need not be in same order): The merge options in Power Query work best if your columns match, even if they are out of order across files.
- Keep the folder clean: By default, Power Query is going to combine all the files in the folder you point to. So keep the folder clean and tight. Don’t copy or create files in the folder that you don’t want to merge.
- Close files before refresh: Power Query refresh can fail or miss the files if you keep them open when updating the query. So close everything before you hit refresh.
- Data Format Issues (especially with Dates): If you have CSVs containing dates and these files use different date formats, the merged file can be a mess and throw date formatting issues. Synchronize date and currency formats across files before merging them to avoid such data format issues.
In conclusion – Use Power Query to Merge your CSVs
Excel’s own Power Query offers a superior, easy and automatic way to combine CSV files. It works beautifully even when combining 1000s of files. For a recent client project, I combined 340 different budget files with Excel Power Query in under 10 minutes. Needless to say, the client’s jaw dropped when they saw the demo!
But for whatever reason, you can’t use Power Query, try either VBA or command line utilities or one of the online CSV combine options.
Bonus: Sample Files & CSV Combine Template
If you need a hand with combining CSV files, download my sample data files and CSV combiner template using below links. Unzip the files and adjust folder path in Power Query (source step of the merged query) to make it work with your computer’s path.
Resources on Power Query
If you want to learn a bit more about what Power Query can do for your data problems, check out below resources:
