How to merge and combine CSV files

Append CSV featured image

Written by Valentine Schelstraete

Jun 15, 2020

CSV files store tabular data (numbers and text) in a plain text format, separated by commas. They can be read and opened with many different programs, but to process and analyze CSV data, you’ll often want to import it into a spreadsheet. If you work in a data-heavy role and environment, you might find yourself downloading multiple CSV files every day. To interpret and read large volumes of raw CSV data, it can be helpful to compile one central spreadsheet. Instead of doing that manually with copy-pasting and manual data imports you can set up an automated system to merge multiple CSVs into a single file.

This enables you to:

  • Pull data from multiple CSV files into one central file
  • Save time — no more manual downloads, email attachments or copy-pasting
  • Collect CSV data from other colleagues and departments automatically
  • Generate automated reports and dashboards with up-to-date data
  • Gather data from CSV files and import it directly into Excel or Google Sheets

How to merge CSV files automatically

Sheetgo works by pulling data from a source file (or multiple source files) into a destination file(or multiple destination files). Your source file(s) remain(s) intact. Changes to the data in the source file are mirrored in the destination spreadsheet each time the connection is updated, so you’ve always got the latest data.

Follow the steps below to automatically combine multiple CSV files into a single file (CSV, Excel, or Google Sheets).

Merge CSV in Excel

Step 1: Install Sheetgo

Install Sheetgo here.

Once you’re inside the web app, click +Create workflow and then Connect to start the CSV file merge.

Give your Untitled Workflow a name at the top of the screen so you can identify it later.

Step 2: Select your source files

To merge several CSV files into one, select Multiple files as your data source.

Click +Select File and locate the first file from inside your cloud storage and click Done.

Click +Add another source file.

When you have added all your source files, click Continue.

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.

In this example, I want to merge data from two files: “investors” and “investments”.

Merge CSV Sheetgo

Under Settings you will find some options to customize the connection.

If you are combining multiple files into one, it can be helpful to identify which source file the data came from. Enable Identify source and Sheetgo will create an extra column next to your data, identifying the origin of the data.

Consolidation tip:

Note that your source files should all have the same layout (e.g. headers and column structure) so that Sheetgo can merge the data correctly.

Step 3: Choose your destination file

Under Send data to, select which file you want to merge, or consolidate, the CSV data in. This can be a new or existing file.

In both cases, Sheetgo will create a new tab(sheet or worksheet) containing the consolidated data.

Existing file

  1. Select the destination file from your cloud storage (note that this can be a different storage from where you selected your source files).
  2. Optional: give the new tab a name of your choice 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. Under Destination, select which file type you want the data to be merged into (Google Sheets, Excel or CSV).
  2. Under Change destination folder, select which cloud storage folder you would like the file to be saved in.
  3. Under File Name, create a name for this new file.
  4. Optional: under New file tab> Tab name, give the tab/connection a name of your choice.

Here, I want Sheetgo to create a new Excel file for me in Dropbox. I name the file “Financials” and the tab “Investors data 2020”.

Merge CSV data destination

Click Finish and save to create the consolidate connection and Sheetgo will start to merge the data.

Check out your destination file: it now contains a tab with the imported data from your source CSV files.

Step 5: Automate the workflow

Now that you have created the workflow and connected your files, you can pull fresh data from the source files into the destination sheet at any time. Just click Run on the floating workflow menu bar. Changes in the source files are reflected in the destination file.

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

Click Automate on the floating menu bar of your workflow. Here, you can choose between daily, hourly, weekly, or monthly updates.

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

Merge CSV automatic updates

Merging data from multiple CSVs

You can use Sheetgo to combine or merge data from up to 80 source files. If you want to consolidate data from a large number of files, consider Consolidating from a folder to automate your work a step further.

To do this, under Source data, select An entire folder. Every time you add a new file to the folder, it will be included in the consolidate connection automatically.

Conclusion

That’s how to merge CSV files into one central spreadsheet automatically with Sheetgo. Note that you can follow the same steps for other spreadsheet formats and it’s also possible to move data across multiple cloud storage platforms.

Looking for more spreadsheet-based automation solutions? Learn how to automatically connect spreadsheets straight inside Dropbox.

You may also like…

Share This