How to build automated reports using scheduled imports of spreadsheets files to an ElephantSQL’s PostgreSQL database!
Have you struggled with manually combining and merging spreadsheet files into a management report, or wasted hours checking through Excel files from your various teams? Those days are over! In this co-authored blog post, we show you how to build automated reports using scheduled imports of your spreadsheets files, with the Holistics platform for business intelligence, and ElephantSQL’s PostgreSQL database service.
For those of you new to the cloud who haven’t got a database, we at ElephantSQL offers a free tier of a small PostgreSQL cloud database instance! It literally takes less than 10 seconds to setup, and will let you use the range of Holistics features to automate your spreadsheet reporting processes.
The Time To Automate Is Now
Thanks to digitisation and the affordability of cloud computing, the way we work are being transformed for good. Yet what many don’t realise is how easy it can be to start off adopting these new tools. Companies that embrace the new digital tools will be far more agile and outperform companies that are stuck with manual processes that waste both time and resources.
The fear of the unknown and resistance to changing the way things are done, can easily be replaced by simple experimentation, as we show you how in this article.
How It Works
The cloud database allows you to have a single, scalable source to accept your various spreadsheet files in a tabular, structured format. This then allows you to manipulate the data stored in the database, to output automated reports, charts and dashboards, which can even be scheduled for delivery via channels such as email, Slack and more!
Setting Up Your Free ElephantSQL Cloud Database
Head on over to the ElephantSQL website login page at http://elephantsql.com/login.html, create an account and select the free tier. Now you just need to give your database instance a name, select the region and data center (typically one that’s closest to you), click create instance and you’re done!
Connecting your new cloud database to Holistics requires just a few details. Click on your new database to pull up its details, and copy over the following information of
- Host,
- Database Name and Username,
- Password and,
- Port.
Importing And Merging Your Spreadsheets With Holistics
Now that your database is connected, you’re ready to begin merging your spreadsheets to create your Holistics reports! The two examples given in the video are:
- Adding date and monthly price data from three different spreadsheets into a single report automatically, using Append mode in Data Imports. [1:10]
- Updating existing records and adding new data from two different spreadsheets into a report, to update the order delivery status from Pending to Delivered or Cancelled. This uses the Incremental Import mode for Data Imports. [2:57]
Holistics Data Imports allows you to easily schedule and move data into your reporting database, with zero coding or data engineering experience required. This lets anyone easily migrate data from database tables, spreadsheets and CSV files, MongoDB, and popular data sources like Google Sheets and Folders, Facebook Ads, Google Analytics and Pipedrive, into your SQL database.
This enables regular users to run processes that would typically require technical expertise to manage extract, transform and load (ETL) data operations, giving you the abilities of a fully trained data engineer!
Once you’ve imported the spreadsheet data you need into a table in your ElephantSQL cloud database, you’re ready to begin automating your reports. The Holistics report editor allows you to point and click you way to generate the visualizations and charts you need, or to even edit the SQL query for greater flexibility.
In the second example in the video [2:57], we created a delivery report to track orders with statuses Pending, Delivered and Cancelled, with conditional formatting to highlight rows such that
- Delivered: Green
- Pending: Yellow
- Cancelled: Red
Now, whenever this Holistics report is opened, it will fetch the latest data from your ElephantSQL cloud database to update the order statuses. And since the import of spreadsheet data has been scheduled to automatically run (i.e. on a daily basis), you can now have a report to monitor the movement of your orders on a daily basis, without having to manually compile a single spreadsheet!
You can read more about using Holistics Data Imports for different data sources and Incremental imports at Holistics documentation page here, and learn how to use the Holistics Report Editor to point and click your way to building your reports on our feature page here.
This is just one example of how you can use ElephantSQL and Holistics to simplify your data work. We’ll have more examples of how you can use cloud solutions to take your business to the next level, so stay tuned!