Everything You Need to Know About Combining CSV Files

First... what even are CSV Files?

CSV stands for Comma Separated Values. It is a format best used for tabular data, rows and columns, exactly like a spreadsheet. A CSV file should have the same number of columns in each row.

A CSV file stores data in rows and the values in each row is separated with a separator, also known as a delimiter. Although the file is defined as Comma Separated Values, the delimiter could be anything.

The most common delimiters are: a comma (,), a semicolon (;), a tab (\t), a space ( ) and a pipe (|). But theoretically anything can be a delimiter as long as it is consistent.

We can see the delimiters in action if we open up a .csv file in a text editor, like Notepad in Windows or TextEdit on Mac.

Wait, how come we can open .csv in a text editor? I can’t open my Excel files in a text editor

This is a great question and requires some diving into what files are.

To a computer, CSV files are the same as text files, with an added requirement that a delimiter is used to break up values in a row. So from a computer’s point of view, a .csv file is the EXACT same as the .txt file.

That’s why you can open and deal with a .csv file in any software designed to handle .txt files.

An Excel file (.xlsx, .xls) is a special format designed specifically to be opened in Microsoft Excel. That’s why you can’t open .xlsx files in any random old software. The file opening software has to be built for .xlsx files.

This is why csv files are the preferred data format for many people. They are easily read in many different softwares.

Ways to Combine CSV Files

Method One: Copy & Paste

This method only works if the files you want to combine are small in size.

While you can do it if you have a lot of files too, it would get very time consuming and tedious for larger files.

However, you can simply just copy & paste the columns you want to combine together into one master file!

Method Two: Command Prompt

This is for macOS, however it is similar for Windows. Here is a tutorial.

1. Add all your CSV files into one folder. I named my folder "reports" & saved it on my Desktop.

2. Open terminal (cmd for Windows) and cd into your folder.

3. Type in cat * .csv > "nameYouWantYourCombinedCSV".csv (copy * .csv file-name.csv for Windows)

4. Go back to the folder you made and you will see the combined file!

Method Three: Using Pandas

If you have a lot of files, this python script may be a good option for you! However, all the files should have the same headers & number of columns.

1. Import packages and set your directory

                
import os
import glob
import pandas as pd
os.chdir("/mydir")
                
                




2. Match the pattern 'csv' using glob

                
extension = 'csv'
all_filenames = [i for i in glob.glob('*.{}'.format(extension))]
                
                




3. Combine all files & export

                
combined_csv = pd.concat([pd.read_csv(f) for f in all_filenames ])
combined_csv.to_csv( "combined_csv.csv", index=False, encoding='utf-8-sig')
                
                

This is a tutorial on freeCodeCamp written by Ekapope Viriyakovithya. Read it here for a more in-depth understanding.

Method Four: Merge Spreadsheets

Merge Spreadsheets is an easy and powerful way to combine CSV files.

You can either Append or Join files.

Append

Join

---

Choose how you want to combine the CSV files

Additional Features - Change Column Names

Select which row the column names are on

Column names are not always on the first row of a file, if they even exist.

If they are on another row, then we need to tell the tool which row they are on. This can be done with the Start at Row option.

If there are no headers in the file, then you can check the Does File Have a Header? checkbox.

The columns will then be renamed in the format: [Filename]_[Column Number].
E.g., if [Filename] is Small-Demo-Data then column 1 will be renamed: Small-Demo-Data_1, column 2 will be: Small-Demo-Data_2...

You can change the delimiter of the file using the Delimiter option.

These options can be selected in the Read File Options under File Options.

Method Five: merge-csv

merge-csv.com is a free online tool to merge csv files.

It is really easy to use but does not have the option to customize or edit your file.

1. Click Select CSV-files or pull your files onto the Drag & Drop field to begin with the merging.

You can change the order of the files.

2. Click the Merge CSV-files button to download your merged file.

And that's it! This is recommended for when you have small files that just need a simple merge.

Method Six: sheetgo

Sheetgo is a no-code tool for CSV files and spreadsheets that enables you to connect and automate data transfer between files.

There are a few pricing plans for Sheetgo but in the free plan you can connect same file types. The next plan is $20 per month but includes filtering by query and more!

If you want the other features and services Sheetgo provides, this is the tool for you.

However for just merging files, there are simpler tools out there. They have an in-depth tutorial on how to use their tool to combine csv files, so please read it here!

An overview of the steps:

Step 1: Install Sheetgo

Step 2: Select the CSV files you want to merge

Step 3: Identify the data source (optional)

Step 4: Choose your destination file

Step 5: Automate the workflow

Go Up

Closing Remarks

Here are 6 methods you can try for combining files. Some require basic coding knowledge while others are easy online tools. Let us know what method you used and any others you'd want to share for us to add!

Feel free to email us at info@lovespreadsheets.com for any questions or concerns.