It is possible to establish a connection to a Microsoft Excel spreadsheet as a data source. However, changes to Microsoft Excel files can break the interface between the spreadsheet and the Visual KPI site.
Excel files are notoriously fragile “databases” because the file name can change, the format can change, column names can change, the worksheet name can change, or the file can be moved. Any of these changes are enough to break the interface.
For this reason, we do not recommend using Excel as a data source, even though we support it. If possible, put your Excel or CSV data in a relational database like SQL Server first..
Best Practices When Using Microsoft Excel as a Database
If you still want to use Microsoft Excel as a data source for Visual KPI, we recommend the following best practices.
Create a production copy and working copy
The “production” copy of the spreadsheet or workbook must be stored in an accessible read-only location that does not change. XLS files cannot be read while opened by any user.
- Users needing to update data should access a version of the XLS file meant for changes.
- Once completed, or on a periodic basis, this version can be automatically copied to the production location to update the production XLS file.
- It is best to write a Windows robo task to copy the source file to a server local folder.
Format using rows, columns and sheets
XLS files can have multiple sheets. Each sheet must have a rectangular format where the top row is a header and at least one column includes a date or date-time value (for example, Datetime, column1, column2, column3, and so on).
Use Visual KPI Designer to verify changes
When changes are made to the overall format of the XLS file, these changes must also be reflected, or at least evaluated, in the Visual KPI Designer interface. Formatting changes that affect the interface include:
- Renaming sheets
- Renaming columns
- Renaming the XLS file
- Moving the XLS file to a new location or changing the path to the file
Data Changes Not Affecting the Interface
The following changes to the XLS file are allowed and do not require updates to the Visual KPI Excel interface:
- Adding rows
- Deleting rows
- Adding Columns
- Reordering columns