Excel as a data source

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:

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.

Open

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

  Open

  

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