How to: Merging CSV files
So you're a CSV "pro" now, and that means you work with data in many CSV files. Perhaps too many? We'll cover now how to merge CSV files together (sometimes called consolidating, or appending) so you can work more efficiently.
Merging files together can be useful in many cases. And, there are many types of merging that you can do!
Whether you are combing a bunch of worksheets spread across various workbooks into a single worksheet, or even just combining worksheets from the same workbook together - there are a LOT of options for how to do this. The best option for you will just depend on a) your comfort level and b) the complexity of the merging you need to do. Let's review!
For most merges, you'll want to be sure that you are working with datasets that have the same structure. This means you have the same number of columns and the same header. This will ensure that when you merge the data together, you don't end up with a level-10 chaotic mess. However, if you are feeling risky and want a challenge, you can opt to merge data together that does not contain the same header/column structure. This guide here goes into detail on how to carefully complete that process.
For most of you, the simplest and least headache-ridden methods will involve combing datasets with the same structure. Before we dive in to your options, let's pause for a little reminder:
Combining data from various CSV files will inevitably (for most) involve viewing/handling the data in a spreadsheet program (like Excel). That means you must not forget the crucial step in working with CSV in those type of programs - import the data in! Don't skip this important step and jump straight to just opening the CSV and start merging data. Importing the data will ensure your data is kept intact and accurate and not mangled up in translation when opening the file.
Okay, let's go.
Whether you are combing a bunch of worksheets spread across various workbooks into a single worksheet, or even just combining worksheets from the same workbook together - there are a LOT of options for how to do this. The best option for you will just depend on a) your comfort level and b) the complexity of the merging you need to do. Let's review!
For most merges, you'll want to be sure that you are working with datasets that have the same structure. This means you have the same number of columns and the same header. This will ensure that when you merge the data together, you don't end up with a level-10 chaotic mess. However, if you are feeling risky and want a challenge, you can opt to merge data together that does not contain the same header/column structure. This guide here goes into detail on how to carefully complete that process.
For most of you, the simplest and least headache-ridden methods will involve combing datasets with the same structure. Before we dive in to your options, let's pause for a little reminder:
Combining data from various CSV files will inevitably (for most) involve viewing/handling the data in a spreadsheet program (like Excel). That means you must not forget the crucial step in working with CSV in those type of programs - import the data in! Don't skip this important step and jump straight to just opening the CSV and start merging data. Importing the data will ensure your data is kept intact and accurate and not mangled up in translation when opening the file.
Okay, let's go.
Basics
Good 'ole copy paste.
You didn't think we'd have a guide on combining data without throwing out the most tried-and-true option first, right? If you are looking to simply append rows of data from one file to another, naturally, you can open your CSV file(s) in a spreadsheet program (like Excel) or even in a text editor, and copy paste the rows you want to add from one file to the other. Simple.
Next level copy paste.
If all you are doing is merging sheets together into the same workbook, you can rely on the built in "Move/copy" feature that most spreadsheet programs have. Open the sheet you want to snag the data from, and right-click "Move/copy" to select where to send the data to.
Good 'ole copy paste.
You didn't think we'd have a guide on combining data without throwing out the most tried-and-true option first, right? If you are looking to simply append rows of data from one file to another, naturally, you can open your CSV file(s) in a spreadsheet program (like Excel) or even in a text editor, and copy paste the rows you want to add from one file to the other. Simple.
Next level copy paste.
If all you are doing is merging sheets together into the same workbook, you can rely on the built in "Move/copy" feature that most spreadsheet programs have. Open the sheet you want to snag the data from, and right-click "Move/copy" to select where to send the data to.
Formulas
Sometimes the basics just don't cut it. If you need a bit more control and/or automation of your merge process, you can always rely on formulas in your spreadsheet software. This can be especially handy if you're not looking to just blanket-copy everything over, but want to only copy data that meets certain conditions. Some examples of Excel formulas that will work to merge data include:
Sometimes the basics just don't cut it. If you need a bit more control and/or automation of your merge process, you can always rely on formulas in your spreadsheet software. This can be especially handy if you're not looking to just blanket-copy everything over, but want to only copy data that meets certain conditions. Some examples of Excel formulas that will work to merge data include:
Excel Data Tools
There are two primary tools in the Excel arsenal that are designed to help you merge data together, and they solve very different purposes.
Consolidate
From your "Data" tab in Excel, you'll see the option for "Consolidate".
There are two primary tools in the Excel arsenal that are designed to help you merge data together, and they solve very different purposes.
Consolidate
From your "Data" tab in Excel, you'll see the option for "Consolidate".
The purpose of this tool is to apply a function to numeric data stored in various worksheets. For example, if you have recorded sales from various store branches, stored in different workbooks, but wish to combine the data to get total sales across all branches. This would be a perfect use case for the "Consolidate" tool. Here is a great guide on how to use it - and you can even link up your source data to ensure that your new, consolidated worksheet is updated anytime there is a change to the source data!
Power Query (or, Get & Transform Data)
This one legitimately sounds intimidating, but it is actually one of the most useful tools for merging data from various files together. Using Power Query, you can follow the step-by-step wizard to combine data in various worksheets together into one, and even merge a whole folder of workbooks into one with just a few clicks. This method is extremely useful when it comes to merging a ton of data together, where it would be too labor-intensive to follow some of the other methods mentioned here. In fact, merging from 2 or 20 sheets takes about the same amount of effort using Power Query - how nice is that! To see it in action, check out either this guide or this one for detailed steps for various use-cases.
Power Query (or, Get & Transform Data)
This one legitimately sounds intimidating, but it is actually one of the most useful tools for merging data from various files together. Using Power Query, you can follow the step-by-step wizard to combine data in various worksheets together into one, and even merge a whole folder of workbooks into one with just a few clicks. This method is extremely useful when it comes to merging a ton of data together, where it would be too labor-intensive to follow some of the other methods mentioned here. In fact, merging from 2 or 20 sheets takes about the same amount of effort using Power Query - how nice is that! To see it in action, check out either this guide or this one for detailed steps for various use-cases.
3rd Party Add-ons/Software
Sometimes you just don't want to deal with the merging task, we get it. There are some very effective solutions out there that alleviate a lot of the burden for you. You can check out websites such as Merge Spreadsheets or add-ons for Google Sheets such as Merge Sheets.
As you can see, there are myriad ways to merge data from CSV files together. Find the one that works best for you and don't be afraid to try different methods for different tasks! The more you master, the easier it will be the next time you're giving the task of merging data together to pick just the right one for the job.
Sometimes you just don't want to deal with the merging task, we get it. There are some very effective solutions out there that alleviate a lot of the burden for you. You can check out websites such as Merge Spreadsheets or add-ons for Google Sheets such as Merge Sheets.
As you can see, there are myriad ways to merge data from CSV files together. Find the one that works best for you and don't be afraid to try different methods for different tasks! The more you master, the easier it will be the next time you're giving the task of merging data together to pick just the right one for the job.