Configuring Excel Connector Sources
You can retrieve data from an Excel worksheet by using
The connector is a built-in connector that is available with Cloud Application Business Insights. This connector is based
on JDBC connector. You can create manual data definitions by using SQL queries to retrieve data from
an excel worksheet. A single manual data definition retrieves data from a single worksheet within a
workbook. Queries to retrieve data from multiple worksheets are not supported. For each workbook,
you need to create a separate connector source.
Before you begin
- Ensure that a worksheet contains a single table. Only the first row of the first table is used as the header row. If you create multiple consecutive tables with separate headers, then all the tables are considered as a single table and data is retrieved for the entire table. If you create multiple tables that are segregated throughout the worksheet, then data is retrieved only from the table that starts at an earlier row.
- Use only alphanumeric characters to name the workbooks, and copy the workbooks at the following
location on the server where Cloud Application Business Insights is
<install_dir>/wlp/usr/servers/prdapp/excel/<file_name>Where, install_dir is Cloud Application Business Insights installation location. By default, it is /opt/icabi.Note:
- You must save the Excel files at the mentioned location. If you change this location, then the file cannot be backed up or restored by using the default backup and restore scripts that are provided along with Cloud Application Business Insights.
- Files that are saved on a remote server cannot be accessed.
Complete the following steps to create an Excel connector source by using
- Open Dashboard Designer.
- In the navigation pane, go to .
On the All Connector Sources page, click Add Source.
An Add Connector Source window is displayed.
- From the Connector Type list, select EXCEL Connector.
- In the Connector Source Name field, enter a name for the excel
Source name can contain alphanumeric characters only.
- In the Excel File Name field, enter the location where the Excel
file is saved.
For example, <install_dir>/wlp/usr/servers/prdapp/excel/<file_name>Where, <install_dir> is Cloud Application Business Insights installation location. By default, it is /opt/icabi.Note: Save the Excel file at the specified location. If you change this location, then the file cannot be backed up or restored by using the default backup and restore scripts that are provided along with Cloud Application Business Insights. See Application backup and restore.
- Click Next.
- In the Query field, enter a sample query for the Excel data
select * from sheet2
- To make sure that the connection to the data source is successful, click Test
Connection. If the source details and the sample request are valid and complete, then a message that indicates that the connection is successful is displayed. For connection failure messages, you must fix the errors that are mentioned in the messages and test the connection again.Note:
- Even if the source details are valid, a connection failure message might be displayed due to an invalid response received for the sample request.
- If you enter a sample query that retrieves larger number of records, then even if the data
source is configured correctly, Test Connection displays an error message,
which indicates that the data source is not connected.
To resolve this issue, you must enter a sample query, which returns limited number of rows and is faster in execution.
- Click Save.
- Connector source name
- Connector type
- Date and time when the connector source was first created, and the username of the user who first created the source.
- Date and time when the connector source was last modified, and the username of the user who last modified the connector source. Only the latest record is displayed.