A dashboard is a type of graphical user interface (GUI) which often provides at-a-glance views of key performance indicators (KPIs) relevant to a particular objective or business process. These kinds of visualizations feature a combination of historic data and real-time information that is useful for identifying emerging trends and monitoring efficiency.

It’s quite easy to produce a data visualization with Excel or another spreadsheet program on static, historical data. However, it’s a lot harder to automatically update a spreadsheet for a real-time dashboard. Why?

Why is it hard to build a live dashboard with Excel?

Excel, along with many other spreadsheet programs, are not databases. They are good for storing small amounts of data records with great flexibility in manual data entries. However, they cannot store a larger amount of organized data while maintaining a good schema, and indexing method for incremental changes in data. Try sorting 1 million rows of data in Excel, it’d easily crash your computer. A database (especially a transactional database such as a SQL database) however can easily load new data records into a large table while keeping everything in order.

To read more on this subject, you can check out this article on the “differences between a database and a spreadsheet”.

Can I create a database for Excel files?

Yes, you can! Even though spreadsheet programs are not great for storing, indexing and modeling large amounts of data, you can easily load them into a SQL database. As long as they share a similar set of columns, you can construct a database for these files.

To read more on this subject, you can check out this article: How to build a database with CSV files?

Now, can we build a live dashboard for the Excel database?

Of course! Once you have an Excel database ready, you can host a live dashboard on it while keeping it updated as long as you load more files into the database.

Here’s how you can do it within a few minutes.

1. Create a “Batch CSV” resource for your Excel files

First, you can create a “Batch CSV” resource for literally batching your CSV files. There's no limit on the size of each file. Though the max number of files currently supported is 100. If your files comply with the following requirements, you can combine them into a Batch CSV.

  • All CSV files should have the same schema (the same number of columns and the same data type for each column). However, the column names can be different.
  • The maximum number of files you can upload at a time is up to 100 files.
  • Currently, Batch CSV only supports .csv, .tsv, and .txt files. Excel files must be converted to CSV
Add a resource - Batch CSV

2. Create a data project for your resource

Once you have the resource created, you can now create a data project to access your CSV files. This is also where you can manipulate and update the CSV files. Here you can apply a series of transformations on your data to deliver the desirable result table.

CSV data project

3. Build a dashboard on the data project

Once you have built your desirable table, it’s now time to turn into a visualization. Simply go to the “Charting App” to visualize your data in the project. There are many charts you can create. For example, the line chart visualizes Apple’s stock prices from January 1st 2010 to December 31 2011. Next, let’s update this chart by simply adding a new CSV file.

CSV dashboard

4. Upload more CSV files

When you have a chart built already, you can update the chart by updating the resource associated with it. Simply click “Add Files” to add more CSV files to the batch. As long as they share the same schema, you can combine the files into one giant table for the project table.

Update Batch CSV resource

5. Update the project

Once your Batch CSV resource is updated, you can now update the project. Once you click the little update button, you can sync the project table with your resource. Once the table is updated, you can see the number of the rows increase at the bottom.

Update CSV data project

6. Watch the dashboard update in action

Once the project table is updated, your dashboard will be automatically updated. Now you can see that Apple’s stock prices are updated from January 1st 2010 to March 10th 2022. There’s no need to perform any action here in the charting app.

CSV dashboard

Can I automate the entire process?

Right now, the dashboard still requires a bit of manual work to update. We’re however working on a feature to turn this into an automated data pipeline. This way, every time you upload new CSV files, the system will recognize the changes and update the database and the dashboard connected with it automatically.

If you’re interested in learning more about this, here’re some way to contact us.