...
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 |
| Info |
|---|
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.
...
| Info |
|---|
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. |
| Info |
|---|
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. |