Everything You Need to Know About Combining CSV Files

by Author Profile Picture Astha Sharma on 2020-06-09

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

  3. Match the pattern 'csv' using glob
    extension = 'csv'
    all_filenames = [i for i in glob.glob('*.{}'.format(extension))]

  5. 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.


Merge CSV Files Append Example


Combine CSV Files Join Example

Choose how you want to combine the CSV files



1. Upload all your files to the tool

Upload CSV Files to Combine

You can always upload unlimited files under a total size of 1 MB. If you want to upload files that have a total size of over 1 MB, you can upgrade to a pro plan for just $4 per month. With the pro plan, you can upload however big files you want.

2. Tool will merge automatically based on column names

Merge CSV Files Automatically

The tool will automatically align columns with the same name. The name must be completely identical. Completely.

The sample of the how the final file will look will appear. It only shows the first row from each file so it might happen that the sample row appears blank.

3. Pick download options and download your merged file

You can pick two options and click Download. You need to give the file a name before downloading.

The file will download to an .xlsx file. You can convert the .xlsx file to CSV after it's downloaded. Check out a guide to convert here.

Picking Remove Duplicates will keep the first row of identical duplicates from the merged file. This will be done automatically when you download. The example below is done manually just for example purposes.

Remove Duplicates from Merged CSV File

Picking Add Source File Names will add a new column with the source file name that a row comes from.

Add Source File Names to Merged CSV File

1. Upload your files to the tool under the join tab

Upload CSV Files to Join

You can always upload unlimited files under a total size of 1 MB. If you want to upload files that have a total size of over 1 MB, you can upgrade to a pro plan for just $4 per month. With the pro plan, you can upload however big files you want.

2. Select the Left file, Right file and Join Type

Select Options to Join CSV Files

You need to select three things in order for the files to join

  • Left File when merging CSV files all the values on the left side will come from this file
  • Right File when merging CSV files all the values on the right side will come from this file
  • Join Type is to select how the two excel files should be combined. It is a slightly advanced topic but nothing you can't wrap your head around with a few examples.

    There are 4 Join Types: Inner, Left, Right and Full. They all merge the file differently

    Join Types

    Read all about joins in our A Brief (yet Comprehensive) Introduction to SQL Joins

3. Select the column(s) you want to join on

Columns on which to Combine Excel Files

Finally, you need to select the column(s) whose values you want to join on. You need to pick the column name for both the left and right files. You can pick as many columns as you want. The joining will be done on the values of all the columns selected.

4. Download your merged file

Download Joined Excel File

Give the file a name and download your merged and joined file.

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.

Join Read File Options

Method Five: merge-csv 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

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 for any questions or concerns.