Excel can be used as a data source, which will load Excel columns and rows into your underlying database as a table. When the data changes in the Excel file the table, and therefore the charts, will also be updated.
This functionality works with SQL Server, MySQL and MariaDB. For other databases, or non-Excel data, you will need to manually load the file into a table.
Your Excel file:
Should be in .xlsx format
Needs to ‘look’ like a database table, i.e., simple rows and columns of data
Whilst it could contain totals and subtotals this isn’t a good idea as they would need to be identified and excluded, i.e., filtered, to stop the data doubling, or trebling up.
If your Excel file contains special characters, they will be replaced when the file is imported. Please see the following list for clarification of how your file might be changed:
Special Character | Replaced With |
|---|---|
- | _ (underscore) |
! (exclamation mark) | _ (underscore) |
& (ampersand) | _ (underscore) |
^ | _ (underscore) |
~ | _ (underscore) |
{ } | _ (underscore) |
[ ] | _ (underscore) |
\ | _ (underscore) |
* | _ (underscore) |
# | _ (underscore) |
@ | _ (underscore) |
| | _ (underscore) |
= | _ (underscore) |
? | _ (underscore) |
+ | _ (underscore) |
“ | _ (underscore) |
` | _ (underscore) |
, (comma) | blank space |
; (semi colon) | blank space |
: (colon) | blank space |
' (apostrophe) | blank space |
/ (slash) | blank space |
( (open bracket) | blank space |
) (closed bracket) | blank space |
% (percent sign) | PERCENT |
Please bear in mind, that if you’re trying to save 2 spreadsheets with the same table name except that the name of one of the spreadsheets contains a special character, they will overwrite each other in the database when they’re imported as they will end up with the same name. For example, both “!test_data” and “&test_data” will be saved as “pi_excel_test_data” to the database. |
In addition, the database user you have configured for this data connection will need to have been granted create and insert privileges, or equivalents, on the ‘target’ database.
To use Excel as a data source, from within the Data Connection you wish to use:
Select the Excel tab
Click the ‘Add’ button
Fill in the path to the Excel file. The file and path need to be accessible from, and are relative to, the dashboard instance, not your client instance
Click the ‘Validate location’ icon , this will show the sheets in the Excel file
Tick the sheet(s) you wish to connect to, ‘Top 500 Songs’ in this example. If you select multiple sheets, each sheet will become a separate table in the database
Click the ‘Save’ icon .
After the Excel file has been added, you will also need to generate the table. Please refer to the section Adding Tables/ Views for more information.
When a table has been created, it will be named ‘PI_EXCEL_your_file_name_your_sheet_name’
When the table has been generated you can setup the object metadata as you would do for any other table. Please refer to Object Configuration for more information.
If you add an additional sheet to the underlying Excel file it will appear in your dashboard and can be used in the same way. To see the new sheet, you will need to press the ‘Validate location’ icon again on the Excel tab.
To add additional Excel files, repeat the process detailed above.
Once this has been done the tables can be treated as any other data table would be, you could for example create a view over it and then point the dashboard at that view, or you could point the dashboard directly at the table(s) in question.
When the dashboard reads from the table it checks if the underlying Excel file has been updated since the last database refresh and, if so, reimports the data automatically. |
The Excel file needs to be accessible by the user that is running the Excel Reader process and by default the user, on Windows, is LOCAL SERVICE. |