How to merge and combine CSV files automatically

Written by
Laura Tennyson

Looking for a quick way to combine data from multiple CSV files into one data set? Tired of manually merging CSV files into Excel or Google Sheets? CSV files are great for storing data but extracting and processing them can be tedious and time-consuming.

CSV files offer a simple and convenient format for storing and sharing data. Lighter than a spreadsheet, they store numbers and text in a tabular, plain-text format. This makes them a versatile choice if you’re moving information from one database to another, or extracting data from an ERP or other applications.

When you’re handling large volumes of raw data, you might want to combine CSV files into one file before feeding that data to another program or analyzing it in a spreadsheet.

In the past, many of us would have done that manually by copy-pasting or importing data one file at a time. These methods are a drain on your time and energy. On top of that, the risk of error and time delays can result in poor quality data.

Instead of collating data manually, it’s quicker and more reliable to set up an automated system that merges CSV files into one for you.

As a bonus, an automated system is dynamic. That means that if the data in your files changes, you can sync those changes to the master CSV file or spreadsheet. No need to repeat any manual work that you’ve already done!

Let’s dive in and see how it works:

Automated CSV merging: how it works

The shift to the cloud has transformed the way we manage and share data.

By syncing your CSV files to an online file storage platform (such as Google Drive, OneDrive, or Dropbox), you can take advantage of the cloud’s automation potential.

What does that mean? Essentially, once your CSV files are online, you can automate data transfer between them in a couple of clicks.

By connecting CSV files you can:

  • Merge multiple CSV files into one CSV file.
  • Pull data from CSV files into Excel or Google Sheets.
  • Import data from colleagues’ CSV files with an automated workflow.
  • Sync changes in CSV files
  • Consolidate CSV data into automated reports and dashboards.
  • Leave the source file intact for full data traceability.
  • Save time — no manual downloads, email attachments or copy-pasting.
merge CSV files 1

How to merge CSV files into one

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

Here I’ll show you how to automatically import and consolidate data from three CSV files into one.

Step 1: Install Sheetgo

  • Open Sheetgo by clicking on the blue button below.
  • Log in with your Google, Microsoft, or Dropbox account.
 

The Sheetgo web app opens on the home screen.

  • Click +Create workflow > Connect
  • Give your Untitled workflow a name so you can identify it later.
merge CSV files 2

In this example, I want to merge CSV data from three auto dealers in Florida, Alabama, and Georgia. I’ll call my workflow Used Car Sales.

Each dealership downloads sales data from their internal system into a CSV file at the end of the month and drops it into the company’s shared Google Drive folder.

The files have the same format, containing data on the model, make, and ID number of each car sold.

merge-csv-files-source-csv-data

Step 2: Select the CSV files you want to merge

  • To merge several CSV files into one, select Multiple files as your data source.
merge-csv-files-select-multiple-source-files
  • Click +Select File
  • Locate the first file from inside your cloud storage folder and click Done.

Here I’ve selected the first CSV file: Florida February, from my Google Drive.

merge CSV files 5
  • Click +Add another source file and select the other CSV files you want to merge.
CSV merging tip:

Note that your CSV source files should all have the same layout (e.g. matching headers and columns) so that Sheetgo can consolidate the data correctly into one sheet.

Are your files stored on your computer?

If you want to connect files that are stored locally (on your computer) you can set up an automated system to back up and sync files from your desktop to your online cloud storage service.

This enables you to create automated data flows using Sheetgo. It also keeps your files secure and allows you to access them from anywhere. Learn more.

Step 3: Identify the data source (optional)

When you’re combining multiple CSVs into one, it can be helpful to identify which source file the data came from.

Sheetgo’s Identify source feature will create an extra column in the destination sheet, identifying the origin of each row of data. To identify the data source in the destination sheet:

  • Go to Settings.
  • Switch on Identify Source.
  • Choose an identify source method: Source location, File name, File creation date, File update data, or Tab name. This is the identifier that will appear in the destination sheet.

Here I’ll choose to identify the source by file name:

merge CSV files 6

When you’ve loaded all of your CSV source files into Sheetgo and applied optional settings, click the Continue button.

merge CSV files 7

Step 4: Choose your destination file

Under Send data to, select which file you want to merge, or consolidate, the CSV data into. This can be

  • A new CSV file or an existing CSV file.
  • A new or existing spreadsheet (Google Sheets or Excel).

Existing file

  1. Select the destination file from your cloud storage (this can be in a different folder from the one containing your source files).
  2. Optional for Excel and Google Sheets: enter a name in the New file tab box. This will be the name of the new tab and the connection. If you don’t enter a name, Sheetgo will automatically name the tab Sheetgo_SourceTabName.

New file

  1. Select which file type you want the data to be merged into (Google Sheets, Excel, or CSV).
  2. Optional: Click Change destination folder if you want to save the file to a different cloud storage folder.
  3. Under File Name, enter a name for the new file.
  4. Optional for Excel and Google Sheets: enter a name in the New file tab box. This will be the name of the new tab and the connection. By default, Sheetgo will name the tab Sheetgo_SourceTabName.

In this example, I want to merge my three CSV files into a Google Sheets file in Google Drive.

I’ll name my destination file Regional car sales and the destination tab February.

merge CSV files 8
  • Click Finish and save and Sheetgo creates the connection between your files.

Here you can see that my new destination spreadsheet (Regional car sales) opens inside Sheetgo. It contains a tab, February, with the merged data from my three CSV source files.

Because I enabled the Identify Source setting, Sheetgo has added Column G: Source to the dataset. The values in this column show which file each row of data was imported from.

merge CSV files 9

Sync the files at any time

  • Click on Workflow to see how your files are connected.

Now that the system is set up, you can update the workflow at any time by clicking Run on the menu bar.

This will pull fresh data from your CSV files into the destination file. If your source files have changed since the last update, those changes will be reflected in the destination sheet.

merge-csv-files-workflow-run-update

Step 5: Automate the workflow

To save time and make sure you’ve always got the latest data, switch on automatic updates.

  • Click Automate on the workflow menu bar and choose how often you want updates.

The data will be refreshed at regular intervals, without you having to open Sheetgo or any of your files.

merge-csv-files-workflow-automate

Merge a folder of CSVs

If you want to combine data from a large number of CSV files at once, try Consolidating from a folder.

Not only is this quicker than selecting each file individually, but you can automate another stage of your work.

Every time you add a new file to the folder, it will be included in the connection automatically. When you update the workflow, data from the new files that you’ve added to the folder will be merged into the destination sheet.

To merge an entire folder of CSV files into one,

  • Open Sheetgo and go to +Create workflow > Connect.
  • Under Source data, select An entire folder.
  • Select the folder from your cloud storage. Remember that every source file must have the same header and column layout.
  • Under Settings, enable Identify source (optional).
  • Select your destination file.
  • Click Finish and Save.

How to append CSV data in a historical log

If you work with dynamic data that changes frequently, you might want to save historical values from your CSV files or spreadsheets. By keeping past records, you can generate historical charts to track changes over time.

Saving different file versions is one way to do this, but it’s slow and difficult to combine all the data.

Instead of generating an unnecessary number of files, you can set up an automated system to append CSV data in one sheet.

Normally, Sheetgo updates the data in the destination sheet every time you refresh a connection. However, if you want to retain previous data entries, just switch on Append.

  • Open Sheetgo and go to +Create workflow.
  • Select your CSV source file(s).
  • Choose your destination file.
  • Go to Settings and switch on the Append data button.
  • Click Finish and Save to create the connection.

Note that to append data correctly, your source file(s) must contain a header.

merge-csv-files-append-csv-data-historical-log

Retain previous CSV data

An automated data appending workflow enables you to:

  • Capture values from a volatile data set.
  • Save old CSV data for future analysis.
  • Generate historical reports and charts.

Every time the connection or workflow is updated, fresh data will be sent from the CSV file(s) to the destination spreadsheet and listed underneath previous entries.

  • To update the workflow manually, click Run on the menu bar.
  • To schedule automatic updates, click Automate and create your own update schedule.

Remember that every time there’s an update, Sheetgo will append a new data entry in the destination file.

If your data doesn’t change very often, reduce the frequency of automatic updates. This will help you avoid duplicated data entries in the destination sheet.

merge CSV files 13

How do I filter CSV data to a spreadsheet?

By default, Sheetgo will import the entire contents of your CSV source file(s) into the destination tab, but it’s easy to customize that with filters.

A filter lets you choose exactly which data you want to transfer. You might only want to extract data from specific columns, or entries from a certain date, for example.

Remember that Sheetgo sends data in one direction, so when you create a connection your source file remains intact. This gives you data traceability, meaning you can always check back to see where your data came from.

To filter CSV to Excel, Google Sheets, or another CSV file:

  • Open Sheetgo and click +Create workflow > Connect.
  • Under Source data, select the CSV file(s) you want to pull data from.
  • Click Continue.
  • Under Filter data, choose to Filter by condition or Filter by query.

Your CSV files must be stored in Google Drive to use Sheetgo filters.

How to query a CSV file

Let’s take a look at an example.

As before, I’ve selected three CSV files from regional car dealerships in Florida, Georgia, and Alabama.

This time, instead of importing and merging all car sales data for February, I only want to import a list of Chevrolets.

To do this, I select Filter by condition and apply the following criteria:

  • Data matches one condition (OR)
  • Column B: Make
  • Criteria: Text contains
  • Value: Chevrolet
merge CSV files 14
  • Click Continue.
  • Under Select destination, choose the CSV file or spreadsheet you want to filter the data to.
  • Optional for Google Sheets and Excel: enter a name in the New file tab box.
  • Click Finish and Save to create the filtered connection.

Here I want to merge filtered CSV data to a new tab called Chevrolet inside a new Google Sheets file called Car sales per manufacturer.

merge CSV files 15

My new destination file, Car sales per manufacturer, opens inside Sheetgo.

As you can see, the connection has imported all Chevrolets from my three regional CSV files.

And because I enabled the Identify source setting, Sheetgo has created an additional column – G: Source – which tells me which source file each row of data came from.

merge CSV files 16

How to split a CSV file into multiple sheets automatically

Now that you know how to merge multiple CSVs into one, you might be wondering if it’s possible to do the opposite. It is — and it works in exactly the same way but in reverse!

By selecting one CSV file as your source, you can distribute that data to multiple other destination files.

This means you can:

  • Split one big CSV into multiple spreadsheets
  • Parse CSV data to multiple Excel or Google Sheets files, based on specific criteria
  • Filter CSV data to multiple spreadsheets automatically
merge CSV files 17

Distribute filtered CSV data

Step 1

  • Open Sheetgo and go to +Create workflow > Connect.
  • Under select source data, choose CSV or TSV file.
  • Select the file you want to split.
  • Click Continue.

Step 2:

  • Under Send data, select Multiple spreadsheets in a folder.
    • To distribute the data to Google Sheets files, select Google Drive as your destination folder.
    • To distribute data to Excel files, select OneDrive or SharePoint as your destination folder.
merge-csv-files-split-csv-file-destination-folder

Step 3:

  • Select a new folder or an existing folder.
  • If it’s a new folder, enter a name in the Folder name box.
  • Select the Source column. Data will be filtered to different sheets according to the values in this column.
  • Optional: adjust the suggested name in the Tab name box.

Note that the tab name will be the same in every destination file. It’s also the name of the connection.

Data will be distributed to a spreadsheet matching the exact name of the value in the source column. If you’re distributing data to a new folder, or a corresponding file does not yet exist for that value, Sheetgo will create a spreadsheet automatically.

Example:

Here I’ve got a big CSV called Sacramento Real Estate Sales. It contains data on property sales in Sacramento, California. Column H: Type indicates the type of property: Condo, Residential, or Multi-family.

I want to split this CSV file into three separate Google Sheets files: Condo, Residential, and Multi-Family.

merge CSV files 19
  • Click Finish and Save to create the connection

Sheetgo creates a new folder in my Google Drive called Property Type. Inside that folder are 3 new Google Sheets files. There’s one for every value in my source column: Condo, Residential, and Multi-family.

Let’s take a look at the Condo file. As you can see, it contains filtered data that’s been exported from the CSV source file:

merge CSV files 20

Automated CSV file splitting

Just like with the merge and filter examples I showed you earlier, your files are now connected in a workflow. This means that if add new data or make changes to the data in the source file, those changes will be synced to the destination file with the next update.

  • Click Run to update the workflow manually.
  • Choose Automate to schedule regular automatic updates.

If you add a new row of data to the source file with no corresponding destination sheet, Sheetgo will create it for you automatically.

So, if I add a new entry to my Sacramento file with the property type: Commercial, Sheetgo will create a new destination file called Commercial and export all corresponding data to that sheet.

You can divide and distribute data from other spreadsheet formats using the same steps. Check out our post on how to split an Excel sheet into multiple worksheets.

Automated data management

That’s how to merge multiple CSV files into one, filter CSV data to another file, or split one CSV into multiple spreadsheets.

Once you’ve started building a workflow, it’s easy to add more connections, incorporate other files, and expand the system.

As well as merging CSV files, Sheetgo also enables you to connect CSV files to Excel, link CSVs to Google Sheets, and build multi-file, cross-cloud workflows.

Check out our other posts below for more spreadsheet automation ideas.

You may also like…