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 from [sheet1$]
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
Notice how it automatically gives a column name. To avoid such non-functional column names we can provide a name in the query itself