How to append CSV and track historical data
If you and your team collect and store large volumes of data in CSV files, exporting all of that information to spreadsheets for analysis is a time-consuming task. And if you handle dynamic data that changes frequently, you may find yourself doing this pretty regularly. Storing your CSV files and spreadsheets in the cloud allows them to be updated in real-time. This is powerful, enabling us to collaborate with others, work more productively and eliminate multiple file versions. However, sometimes you may want to retain a copy of your data at certain points in time, especially if you need to create historical reports or records.
Often the solution for this is to create multiple versions of files or to copy-paste data. That works, but it’s a slow and error-prone process which can also generate an unnecessary number of files. Instead, you can monitor changes to your CSV data over time and retain a “snapshot” of values at regular intervals by creating an automated CSV workflow. This enables you to monitor constantly fluctuating values such as product prices or sales data by generating an automated log of historical changes over time. The data will flow straight from your CSV file into a spreadsheet.
This means you can:
- Capture data at regular intervals
- Keep old CSV data for future analysis
- Move data from CSV to Excel or Google Sheets automatically
- Generate historical reports, charts and dashboards to analyze time-related changes
- Eliminate copy-pasting or manual work with an automated system — no coding required
Read on for a step-by-step guide on how to append CSV data and create a historical record of changes to your data.
How to append CSV data in Excel
Sheetgo enables you to transfer data from a CSV file directly into a spreadsheet automatically. Here I’ll show you how to append CSV data at regular intervals, creating an automated log of historical changes. Readings of the CSV data are “logged” in the spreadsheet.
In this example I’ll show you how to append CSV data in an Excel file, but this process also works for Google Sheets. Just follow the same steps, selecting Google Sheets as your destination file.
Step 1: Install Sheetgo
Once inside the web application, click +Create workflow and then Connect.
At the top of the screen, give your Untitled Workflow a name so you can edit and identify it later.
Step 2: Select CSV as your data source
Firstly, select your data source. This is the file containing the dynamic, frequently changing, data. In this case, we select CSV file.
Locate the file from your cloud storage platform. In this example, my CSV source file is called “Daily stock information”.
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: Select your destination file
Choose which type of file you want to send the CSV data to. This is the file where your data will be appended, creating a historical record of changes.
Option1: Send to an existing file
You can select an existing spreadsheet from your cloud storage. Sheetgo will create a new tab(worksheet) in this spreadsheet, containing the data imported from the CSV file. If you wish, rename the tab in the New File Tab box. If you don’t enter a name, Sheetgo will automatically call this tab Sheetgo_SourceFileName.
Option 2: Send to a new file
You also have the option to send the CSV data to a new spreadsheet. In this case, Sheetgo will create a new file for you automatically. Just select which cloud storage folder you want it to be saved in.
In the File Name box, enter a name for this new spreadsheet. Again, you can give a name to the tab in the New File Tab box.
In this example, I will append my CSV data in an Excel file in Google Drive.
I don’t enter a name for the tab, so Sheetgo automatically generates the name “Sheetgo_Daily stock information.csv”.
Step 4: Enable the Append feature
Under Destination, go to Settings. Switch on Append.
Every time the connection is updated, a new data entry from the CSV source file will appear in the destination spreadsheet, appended beneath previous entries. Over time, this will generate a historical log of changes to your data.
Click Finish and save to create the connection between your CSV source file and the destination spreadsheet.
Your workflow has now been created. In Sheetgo, click on Files to see the connected file. Open the destination spreadsheet and you can see that it contains a new tab containing the imported CSV data.
Each time the connection is updated, fresh data will be sent from the CSV file to the destination spreadsheet and appended under previous entries.
To update the connection manually, click Run on the floating workflow menu. Remember that each time you do this, a new entry will be created in the destination tab.
Step 5: Automate the workflow
To automate this process, click Automate on the menu and select how frequently you want automatic updates.
This can be hourly, daily, weekly, or monthly and you can specify the precise time of day, or the day of the week.
Note that every time there’s an update, Sheetgo will append a new data entry in the destination file. If your data doesn’t change too often, reduce the frequency of automatic updates to avoid duplicated data entries in the spreadsheet.
Here I want to analyze daily stock prices fluctuations, so I schedule Daily updates.
Append CSV data in a spreadsheet automatically
That’s how to append CSV data and create a historical record of your data — in Excel or Google Sheets. Sheetgo enables you to create an automated record of dynamic CSV values so you can monitor changes to your data and spot fluctuations or trends.
Do you handle large volumes of data in CSVs and want to move specific information to Excel or Google Sheets? Learn how to filter CSV to another spreadsheet automatically.