How many hours have you spent importing CSV files into Excel or Google Sheets? Or manually extracting and combining data from multiple CSV files into one sheet? Chances are — a lot. CSV files offer a simple and convenient format for storing and moving data between software, but getting your data out of them and into a spreadsheet for analysis is cumbersome. Luckily you can eliminate this time-consuming task by setting up a workflow to merge CSV files into one central spreadsheet automatically.
Once your CSV files or spreadsheets are stored in the cloud you can create dynamic connections between them with Sheetgo. This enables you push and pull data between files while leaving the source file intact. As a result you can:
- Consolidate data from multiple CSV files into one central sheet
- Eliminate copy-pasting and manual file downloads or imports
- Pull up-to-date CSV data into Excel or Google Sheets automatically
- Gather data from colleagues and other departments without emails or back-and-forth communication
- Generate automated reports and dashboards
How to merge CSV files into one
Step 1: Install Sheetgo
Once inside the web application, click +Create workflow and then Connect to start creating your first connection.
At the top of the screen, give the Untitled workflow a name so that you can identify it later.
In this example, we will consolidate multiple CSV files into one spreadsheet tab.
Step 2: Select multiple CSV files as your data source
Select your data source. In this case, select Multiple files.
Find the CSV files that you want to merge (consolidate) from inside your cloud storage. Add a file, then click +Add another source file.
Note that Sheetgo can only merge data from multiple CSV files correctly if all source files have the same layout and column structure. For example, if you have a column called “Name” it should be in the same position in every file.
When you have added all the CSV files you want to merge, 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.
If you want to be able to see where the consolidated data came from, Sheetgo will create an extra column in the destination sheet, indicating the origin of the data. Go to Settings > enable Identify Source.
Step 3: Select your destination file
Next, select which file you want to send the data to. All your CSV data will be consolidated (merged) into this file.
This can be an Excel, Google Sheets or CSV file. It can be an existing file or a new file. In both cases, Sheetgo will create a new tab(sheet/worksheet) containing the consolidated data.
- Select an existing file from inside your cloud storage (note that this can be a different location to where you selected your source files).
- Optional: under New Tab Name, enter a name for the new tab. If you don’t enter a name, Sheetgo will automatically name the tab Sheetgo_SourceTabName.
With this option, Sheetgo will create the spreadsheet for you automatically.
- Under destination, select which file type you want to send the data to.
- Select New file and choose which cloud storage platform you want it to be saved in under Change destination folder.
- Optional: enter a name for the tab under New Tab Name.
In this example I want to merge my CSV files into a new Excel file stored in Dropbox. I call the new file “CSV merge” and I name the tab “Investments”.
Click Finish and save to create the connection.
Check out your destination spreadsheet: you can see Sheetgo has merged the CSV files into a single tab.
Step 4: Automate the workflow
Now you have created the workflow, you can update (refresh) the data in the destination file at any time by clicking Run on the floating menu bar. Any changes to the data in the source sheets will be updated in the destination sheet.
To automate this entire process and ensure you’ve always got the latest data without opening Sheetgo or any of your files, go to the floating menu bar on your workflow and click Automate.
Schedule automatic updates as frequently as you need: hourly, daily, weekly, or monthly. You can also schedule the precise time and day of the week.
Merging a large number of files?
Sheetgo allows you to consolidate data from up to 80 files into one tab. If you have a large volume of source files, try consolidating from a folder to streamline and automate another stage of your workflow.
Under Source Data, select An entire folder.
This enables you to add new source files quickly and easily. Just drop a file into the source folder and it will be included in the connection automatically.
Automated CSV merging
That’s how to merge CSV files into a single spreadsheet automatically using Sheetgo. You can build on your workflow by adding more connections to other files.
If you handle a lot of dynamic data, check out how to generate an automated record of changes to your data by appending CSV files and creating a historical track of your data.