Microsoft Excel Files can be used as database to store information and this data can be visualized on the dashboards.
In order to bring data from Excel to the dashboards there are some basic rules to be followed
Organize your data
Rules to be followed
The first row in excel should be the column names
Make sure the column names contain only alphanumeric characters
It should not contain any special characters
The worksheet name should contain only alphanumeric characters
There should be no blank characters
Make sure the first row immediately following the column names contains actual data (this will dictate what type of data the column contains)
If your data is all around in Excel, then create a named range for the section
You can have multiple sheets within excel and each worksheet is treated like a database table.
For our example, consider this simple Excel file
It contains one sheet1, two columns (period and amt)
Create a connection the Excel File
Go to Actions >> Manage Data Sources/Connections
Define Connection Handle
Give a proper name to the connection handle
Give the exact path to the xls file on your windows machine
Select what type of windows and Excel version you have
Click on "Add new Connection Entry"
Once clicked it will show the above successful message. If you had a previous connection handle defined with the same name then it will overwrite it.
How to Query your Excel File
Drag a grid component from left to the right panel
Right click on the Grid and select "Data Source"
From the drop down, select the newly defined connection
NOTE: You will need to refresh the application from the browser if you don't see your connection
Enter the Query (SQL)
NOTE: Before you execute the query make sure your excel file is closed. Excel files are locked when open and we cannot query it. So close the excel file.
Each worksheet is referenced like [sheet_name$] and you can execute quite advanced SQL against this table.
For our case the sql would be to list all the columns
select * from [sheet1$]
The [*] in the above query tells it to bring all the columns in the worksheet
Click OK to execute the SQL
As you see the grid is now populated with the data from the sheet
How to select specific columns from the Sheet
select period , amt
The above will result in the same display as above as it contains only those two columns
If you select only period you will get this
You can perform aggregate functions as well
Notice how it automatically gives a column name. To avoid such non-functional column names we can provide a name in the query itself
Dial Chart Example
Drag a dial chart to the center
Right click to select "Data Source"
this time we enter average of the amount
You can modify the dial by double clicking on it. Refer the Dial section for detailed explanation on how to modify it
NOTE: The visualizations behave the same irrespective of what data source you choose. So the techniques shown in other sections such as MySQL or other examples are perfectly applicable to Excel Dashboards. If you need help with writing SQL queries then you can leverage the visual query builder of InfoCaptor Desktop