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:
15 Responses to “Compare 2 Lists Visually and Highlight Matches”
Hi,
I solved this in a little different way.
We have 2 lists, one starts at A1 and other at B1, both are vertical arrays.
First thing is define 2 named ranges, list1 and list2:
list1 refers to "=OFFSET(Sheet1!$A$1;0;0;SUMPRODUCT(--(Sheet1!$A$1:$A$1000""));1)"
list2 refers to "=OFFSET(Sheet1!$A$1;0;0;SUMPRODUCT(--(Sheet1!$B$1:$B$1000""));1)"
this way lists will be dynamically sized when you had or remove elements (you can't have blanks and you can't have more than 1000 elements).
Then I use conditional formatting in column A when this formula is true:
"=NOT(ISERROR(MATCH(A1;list2;0)))"
and "=NOT(ISERROR(MATCH(B1;list1;0)))" to list2.
This way we eliminate the need for auxiliary columns or lists.
Hope you like my way! 😀
Nunes
Simple conditional formatting formula.
Assuming lists vertical lists starting in A1 & B1
To highlight just one column (assume B for example)
Conditional formatting>New Rule>by formula
=MATCH(B1,$A$1:$A$99,0)
Set the cell fill to what ever color you prefer & press OK
To highlight both columns repeat with this formula for cell in column A
=MATCH(A1,$B$1:$B$99,0)
This approach doesn't require named fields or addtl columns
glw
Say I had 1 list in A2:A20 and another in B2:B20.
To format all the items in column A that are repeated in column B I would use the following Conditional Formatting rule.
=IF(ISNA(VLOOKUP(A2,$B$2:$B$20,1,false)),true,false)
All the duplicates are highlighted. It us a very simple example of comparison.
I may be missing something here, but I usually highlight both my lists by holding ctrl eg A1:A20 E10:E40 then choose conditional formatting from the ribbon and then highlight duplicates, and this does it?
Lee, I was perplexed as well. I do the same thing you do with the conditional formating. A drag and click to highlight range and choose highlight duplicates does the trick for me.
I believe these methods are to check if an item from one list also appears in the other list. So if an item mentioned many times in one list if also mentioned in the other list or not.
The Conditional Formatting highlight duplicates feature will do this, but it will also highlight an item if it appears multiple times in the one column or list.
Hi, I would just like to know (if you are willing to share) which image editing program you use to make your image like above, like they are torn apart from bottom? I've been looking for long.
@i48998
Chandoo is on Holidays, but Chandoo uses Paint.Net
Paint.net is a free download available at http://www.paint.net/
.
I use CorelDraw/PhotoPaint
.
We both use the Snipping Tool (a freebe with Win Vista/10)
.
We both use Camtasia for doing screen captures to make animated GIFs where you see animation.
Here is how I would accomplish
(1) Define Names: List_1, List_2
(2) =ISNA(MATCH(D4,List_2,0))-1 (Conditional Format formula List_1)
(3) =ISNA(MATCH(D4,List_1,0))-1 (Conditional Format formula List_2)
ISNA will return 1 if NO Match and O if Match by adding a -1 will make: NO Match 0 and Match a -1 which is True
Hi all
this my first Post here
i think we can take Unique List for tow list to know what is not Duplicate By this Array formula
=IFERROR(INDEX($D$6:$D$33,SMALL(IF(ISERROR(MATCH($D$6:$D$33,$B$6:$B$33,0)),ROW($D$6:$D$33)-ROW($D$6)+1),ROWS($J$5:J5))),"")
and this one for Duplicate Value
=IFERROR(INDEX($D$6:$D$33,SMALL(IF(ISNUMBER(MATCH($D$6:$D$33,$B$6:$B$33,0)),ROW($D$6:$D$33)-ROW($D$6)+1),ROWS($J$5:J5))),"")
Don't forget to Enter This Formula by Pressing Ctrl+Shift+Enter
without wanting to ruthlessly self promote here, I do have an addin that does neatly compare two ranges, not just in columns, so you might want to check that out.
Having said that this is a pretty neat solution if you dont want to be going down the VBA or purchase route. I like it
however, could you not do something with the remove duplicates feature in Excel 2010 and then compare the resulting data set?
Hi, Chandoo! I've found yesterday your Excel website... What can I say? It's just awesome, Excellent. Being a developer for 30 years, more than 15 with Office products, and wow!, how many things I discovered in a couple of hours, and what pretty resolved.
I decided to take the long path of the newbies and read all your examples and write down by myself all of them, and when I arrived to this (the comparison of two lists) I think I've found a problem:
a) in "Step 4: Apply conditional formatting to Second List - Use the same logic, but this time the rule becomes =COUNTIF(count1s,$H6)" it should say "Step 4: Apply conditional formatting to Second List - Use the same logic, but this time the rule becomes =COUNTIF(count1s,$H6)>0", but this is a typing error that I believe all of us here might have discovered and corrected
b) the very problem: I wrote down two different lists, in different ranges, and with different number of elements, I specified the equivalent conditional formats, et non voilá!, I didn't get what expected. So I downloaded your example book, I checked range names, formulaes, conditional formats and all OK. So I copied -just values- from my book to yours, and I still couldn't achieve the goal.
I'm using Excel 2010 in spanish, I'm from Buenos Aires (Argentina), and my book is at your disposition whenever you considerate it appropiate.
Thanks in advance for your time, and again my congratulations for your work here.
Best regards.
SirJB7
Comparison of 2 lists visually with highlights
Author: SirJB7 / Date: 11-Dic-2011
Pros: no duplicated tables, no matrix formulaes, no named ranges, no VBA code, just conditional formatting
Cons: not found yet, comments and observations welcome
Features:
a) standard problem: highlights in orange/yellow elements existing in the other list
b) optimized problem: idem a) plus highlights in red/violet first occurrence of elements existing in the other list
Sheet contents:
a) conditional format, 1 rule per list (2 methods used)
A1:A20, first list
B1:B20, second list
a1) range A1:A20, condition =NO(ESERROR(BUSCARV(A1;B$1:B$20;1;FALSO))), format Orange ---> in english: =NOT(ISERROR(VLOOKUP(A1,B$1:B$20,1,FALSE)))
a2) range B1:B20, condition =CONTAR.SI(A$1:A$20;B1)>0, format Yellow ---> in english: =COUNTIF(A$1:A$20,B1)>0
b) conditional format, 2 rules per list (2 methods used)
D1:D20, first list
E1:E20, second list
b1) range E1:E20, condition 1 =Y(NO(ESERROR(BUSCARV(D1;E$1:E$20;1;FALSO)));COINCIDIR(D1;D$1:D$20;0)=FILA(D1)), format Red ---> in english: =AND(NOT(ISERROR(VLOOKUP(D1,E$1:E$20,1,FALSE))),MATCH(D1,D$1:D$20,0)=ROW(D1))
same range, condition 2 and format 2, same as a1)
b2) range E1:E20, condition =Y(CONTAR.SI(D$1:D$20;E1)>0;COINCIDIR(E1;E$1:E$20;0)=FILA(E1)), format Violet ---> in english: =AND(COUNTIF(D$1:D$20,E1)>0,MATCH(E1,E$1:E$20,0)=ROW(E1))
same range, condition 2 and format 2, same as a2)
Personally I like the a2) and b2) solutions, I think the formulaes are prettier.
I still don't know the rules of this website and forum, but it any precept is infringed I'm willing to share the workbook with the solution. If it breaks a rule, I apologize and promise that won't happen again.
Best regards for all!
Dear All i have a complicated situation...
1. I have two sheets of data Sheet1 and Sheet2 (from various sources) - Both of these contain data matching and Not matching as well..
2. Now for me i need to build an excel where in i need to get sheet 3 with values that are present in a column of Sheet 1.
What ever Sheet 1 doesn't have i dont want those rows from sheet 2 to be populated into Sheet3.
Can any one help me out.
Hi Team
The above example is to compare partial name from 2 different columns.
If I want to cross check it in a single column. I have both correct and partial correct/match entries in a column. Is there any way I can find both the entries in the column.
Regards