Microsoft Excel as a Data Source

IMPORTANT: Due to the removal of ODBC functionality in Java 1.8, iDashboards will be retiring support for ODBC. The following applies only to iDashboards v9.1 and earlier, or later versions which are running 32-bit Tomcat and Java 1.7.


iDashboards can connect to Microsoft Excel files using two different methods. The preferred method is to use the iDashboards Excel Autodirectory, which uploads a copy of your Excel file to the iDashboards server. iDashboards can also connect via a direct ODBC connection to an Excel file. Both methods of connections are explained in this document.

Prerequisites

Download and install the appropriate Microsoft ODBC driver for your version of Microsoft Excel. This driver should be installed on your iDashboards server.

• For Excel 2013, download and install the 32 bit Microsoft Access Database Engine 2010 Redistributable from here: https://www.microsoft.com/en-us/download/details.aspx?id=13255
• For versions of Excel prior to 2013, download and install the 32 bit Microsoft 2007 Office System Driver: Data Connectivity Components from here: https://www.microsoft.com/en-us/download/details.aspx?id=23734

iDashboards requires that at least one named range be defined in your Excel file before it may be used as a data source. A named range in an Excel file is viewed by iDashboards similar to how it views a table in a database. To create a named range in your Excel file, highlight the data that you wish to be used in iDashboards and type a name in the Name Box (circled in red in the following screenshot).

Once you have created your named range, make sure to save your file. You can modify and add additional named ranges using the Name Manager under the Formulas tab. For more on creating a named range, check out the videos available at the URL below:

https://help.idashboards.com/data/namedrange/

Creating a Data Source using the Excel Autodirectory

When a Microsoft Excel file is opened, a lock is created on the file which prevents any other users from accessing it. This locking feature can prevent dashboards connected to an Excel file from loading, and users from editing Excel files while a dashboard is loading. To prevent this, it is recommended that the iDashboards Excel Autodirectory feature be used. The Autodirectory is an easy to use upload tool in the iDashboards Admin Application that creates a copy of an Excel file and places it onto your configured Excel Autodirectory folder. By default, this folder is located in your ivizgroup home directory on your iDashboards server.

To add an Excel data source using the Autodirectory, follow these steps:

1. Log on to your iDashboards Admin Application and click on the Data Sources tab.

2. Click the Excel Autodirectory… button.

3. In the Excel Autodirectory page, click Choose File to select your Excel file that you wish to upload and then click Upload Excel File.

4. You should now see your Excel file listed under Data Sources in the User Application’s Chart Designer. The named ranges that you defined in your Excel file will be listed under Select a Table or View.

Creating a Data Source using a direct ODBC Connection

It is also possible to connect iDashboards directly to an Excel file using an ODBC connection. This method of connection is not recommended, as it will cause file locking issues as explained in the Overview. If a direct ODBC connection to your Excel file is required, follow these steps to make the connection:

1. Create a 32 bit ODBC connection to your Excel file. To create a 32 bit connection, use the following tool on your iDashboards server: C:\Windows\SysWOW64\odbcad32.exe

2. In the 32 Bit ODBC Data Source Connector, select System DSN, and then click Add.

3. Select the Microsoft Excel Driver (*.xls).

4. Browse to your Excel file to create your ODBC connection.

5. Log on to your iDashboards Admin Application and click on the Data Sources tab.

6. In the (new) dropdown menu, select Generic ODBC and click Add Data Source

7. In the Add Data Source screen, enter a Data Source Name (for reference in iDashboards only) and the DSN that matches the name you gave your ODBC connection earlier. Select Allow Custom Queries if you wish to write your own SQL queries when building your charts. Click Save. Your new data source is now available.

Creating a Data Source using the Data Hub

The steps below assume that you have already completed the configuration process that connects the Data Hub to the Data Hub database. If you have not already completed this process, you will be presented with a configuration screen instead of the Upload Files button. If this is the case, skip to the Data Hub database configuration section below to configure your connection before going through this section. You will of course also have to have a valid Named Range associated with the Excel file before you will be able to upload it.

1. Log on to your iDashboards Data Hub Application and click on the Excel Data tab.

2. Click the Upload Files button.

3. In the new window, browse to the needed Excel file and click Open. You can select multiple Excel files to upload at the same time if required.

4. In the new window, select the needed file(s) and click Upload. The application will process for a moment and then refresh the window. The green check mark verifies that the Excel file has been successfully uploaded.

You will then be able to see the uploaded file in the main Excel Data Window along with your other uploaded Excel files.

Configuring the Data Hub database connection

1. Log into the Data Hub application and click the Excel Data tab.

2. A new window will appear stating that you need to configure the Data Hub database connection. Click OK and then click the Edit Configuration button.

3. In the new window, select your data source type and fill in the needed information to connect to the database.

4. Click Save.