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.
What’s more, 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 go through a quick step-by-step on how to merge CSV files automatically.
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. Once your CSV files are online, you can automate data transfer between them in a couple of clicks.
Why use Sheetgo to automatically merge your CSV files?
Sheetgo is a no-code tool for CSV files and spreadsheets that enables you to connect and automate data transfer between files. Once your connections are created, any updates made in the source files will be transferred into your newly merged file. As a result, there is no need for any manual updates.
By connecting CSV files you can:
- Merge multiple CSV files into one CSV file: Combine your CSV files into one master sheet for easier data management.
- Pull data from CSV files into Excel or Google Sheets: Transfer your CSV data into Excel or Google Sheets to make the most of their functionalities.
- Import data from colleagues’ CSV files with an automated workflow: Manage multiple sources of data with ease by creating a workflow that automatically transfers various data into one location.
- Sync changes in CSV files: No need for manual updates – whenever your original CSV files change, so will your newly connected files, so you always have the latest version of data.
- Merge CSV data into automated reports and dashboards: Sheetgo helps to turn your raw data into actionable insights by offering a way to generate reports and dashboards based on your CSV data.
- Leave the source file intact for full data traceability: Sheetgo always keeps one source of truth, so that you always have a reliable data trail.
- Save time — no manual downloads, email attachments, or copy-pasting: With an automated workflow, all you need to do it connect your files and schedule updates. After that, Sheetgo does everything for you!
Now that you understand the benefits of automatically combining your CSV files with Sheetgo, let’s go through a simple step-by-step on how to merge your CSV files using the Sheetgo web app.
How to merge CSV files into one
Let’s imagine I am head of sales and I need to analyze the latest subscription levels for my business in order to create a detailed report. I have multiple CSV files that contain different data relating to the subscriptions, including customer information and latest subscription invoices.
In order to evaluate this data as a whole and turn it into a high-quality report, I need to merge my CSV files together to create a master file. With this, I can apply the calculations I need to do in order to get the latest insights on customer subscriptions.
Let’s take a look at how I can do this with Sheetgo.
Step 1: Install Sheetgo
To access the Sheetgo app, simply log in or sign up. Signing up to Sheetgo is easy – simply log in with your Google, Microsoft, or Dropbox account. Click on the button below to sign up for free!
Sheetgo
Step 2: Upload and sync files to an online storage folder
In order to automate data transfer between your CSV files and any other spreadsheet files, you’ll need to make sure you have activated the synching between your files and an online storage folder. This way, any changes made to your source file will automatically be transferred to your new destination file. It also keeps your files secure and allows you to access them from anywhere. Learn more.
Step 3: Create a new workflow
It’s time to start connecting your CSV files. Head to My workspace in the Sheetgo web app, and click +New workflow. Under Create a custom workflow section, click Create connection.
Step 4: Select your source files
Select your source file. As we are importing multiple CSV files, click the Multiple files option.
Please note: Don’t forget to name your workflow at the top of the screen!
Now it’s time to upload your file. Click +Select file and browse to find your CSV. You can change the online storage file on the left-hand side of the pop-up. Double-click to select your file.
Click +Add another source file to continue adding all of your CSV files.
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 merge the data correctly into one sheet.
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. The Identify data feature will create an extra column in the destination sheet, identifying the origin of each row of data.
Under your uploaded source files, expand the Settings menu.
Switch the Identify data button on to add the new column.
Choose an identifier: Source file location (cloud storage folder), Source file name, Source file creation date, Source file update date, Source tab name, or Destination file update date and time.
Once you’ve loaded all of your CSV source files into Sheetgo and applied optional settings, click Next.
Step 6: Select Merge process
Scroll to the Process section and select Merge.
In order to make sure your CSV files are formatted correctly, you need to select which row to use as the header. Alternatively, if your CSV files do not contain headers, select the Remove header option.
Once finished, click Next.
Step 7: Choose your destination file
Scroll down to the Send to section. Select which file you want to merge 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).
Import CSV files to an existing file
Select the Existing file option, then click +Select file to upload your destination file. Sheetgo will automatically create a new tab for your imported data – simply rename the tab if needed.
Import CSV to a new file
If you choose to import the CSV data to a new file, Sheetgo will create it for you automatically. Select which file type you want the data to be merged into (Google Sheets, Excel, or CSV). Click Change destination folder if you want to save the file to a different cloud storage folder.
Under File name, enter a name for the new file. If you choose to create an Excel and Google Sheets file, enter a name in the New file tab box. This will be the name of the new tab and the connection.
Step 8: Complete the connection
To save your connections, click Finish and Save.
If I open my new file, you can see my newly merged data. Because I enabled the Identify data setting, Sheetgo has added a new column to highlight the source file name. The values in this column show which file each row of data were imported from.
Step 9: Sync the files
Your CSV file is now merged via a Sheetgo connection. This means that you can sync changes from your original CSV files into your newly merged file whenever you like. You can either manually or automatically sync your files.
Option 1: Manually sync files
In Workflow, click on the Run button to update your connections and sync your files.
When you run the connection, data in the destination file will be refreshed with the latest data from the CSV files. For this reason, it’s best not to edit or analyze your data inside the Sheetgo tab because changes will be removed when the connection is updated.
To work on the imported data, transfer it to another tab using formulas.
Option 2: Automatically sync files
In Workflow, click on the Automate button. Activate the Run automatically button and create your own automation schedule. You can choose what days to automate your workflow, up to every hour. Once you’ve created your schedule, click Save.
And there you have it! Your workflow is complete and ready to use! To merge your CSV files automatically, you can sign up for free to Sheetgo by pressing the blue button below!
Merge a folder of CSVs
If you want to combine data from a large number of CSV files at once, try merging 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 the Sheetgo web app and create/select your workflow within the My workspace area.
- Under Source data, select the Files in a folder option.
- 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 transfers the data in the destination sheet every time you refresh a connection. However, if you want to retain previous data entries, just switch on the Append data setting. Note that to append data correctly, your source file(s) must contain a header.
1. In the Sheetgo web app, create your workflow as listed in the steps above.
2. If you want to edit an existing workflow, head to the Workflow area and expand the sidebar on the right-hand side. In the Connections tab, click the more options button (⋮). Click Edit.
3. Under the Destination section, expand Settings.
4. Switch on the Append data button.
5. Click Finish and save to finish creating a new workflow, or Save changes to update an existing workflow.
Why 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. Remember that every time there’s a transfer, Sheetgo will append a new data entry in the destination file.
Quick tip: If your data doesn’t change very often, reduce the frequency of automatic transfers. This will help you avoid duplicated data entries in the destination sheet.
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.
Please note that your CSV files must be stored in Google Drive to use the Query filters. The Cell color filter is not applicable to merged files.
To filter CSV to Excel, Google Sheets, or another CSV file:
1. Open the Sheetgo web app and create/select your workflow within the My workspace area.
2. Under Source data, select the CSV file(s) you want to pull data from. Click Next.
4. In this example, let’s filter my data by condition. Adjust the settings to create a condition, specifying the criteria and value.
5. Once finished, click Next.
6. Under Destination, choose the CSV file or spreadsheet you want to filter the data to.
7. Click Finish and save to create the filtered connection.
Now I have filtered my data, I will have a new spreadsheet containing only the rows of data that match my conditions.
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 split that data into multiple other destination files. The Sheetgo web app allows you to:
- 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
Step 1: Create a new workflow
Head to My workspace in the Sheetgo web app, and click +New workflow. Under Create a custom workflow section, click Create connection.
Step 2: Add source file
Under the Source section, select Single file. Click +Select file and browse to find the file you want to split. You can change the online storage file on the left-hand side of the pop-up. Double-click to select your file. Click Next.
Step 3: Select Split process
Under Process, select Split. Choose the column that you’d like to use to split your data. In this example, I will split my data based on the subscription type. This means that I will have 3 new spreadsheets for each of the 3 different subscription types. Click Next.
Step 4: Add destination file
Under the Destination section, choose whether to split your file into a Single file (split data into multiple tabs within a spreadsheet), Multiple files (split data into multiple spreadsheet files), or Files in a folder (split data into multiple files within the same folder).
You can opt to create a New file for your split data, or choose an Existing file. Edit the file name and destination folder according to your needs. In this example, I’ll create a new folder for my split data.
Press Finish and save.
Data will be split 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.
You too can start merging CSV files with the Sheetgo web app by clicking the button below.
Sheetgo
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 transfer.
If you add a new row of data to the source file with no corresponding destination sheet, Sheetgo will create it for you automatically. Check out our post on how to split an Excel sheet into multiple worksheets for more information.
Automated data management
And there you have it! 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. You can also find other related blog posts below.
