Creating Data Extract from MS Excel

Used for retrieving data from MS Excel workbooks or spreadsheets, where for each selected sheet, the first row contains the field/column names, and subsequent rows contain the data.

NOTE

In production use, it is not advised to use a single Excel file as multiple Panopticon data sources. This is because, when using the same Excel file with the data on several sheets, conflicts may occur in reading the file. A workaround could be to set up a Data Extract with scheduled refresh for each of the datasets in the Excel file, and then let the data tables in your workbook load the data from the Data Extracts.

Steps:

1.     On the New Data Extract page, select MS Excel in the Connector drop-down list.

2.     Select the Excel file source:

·         File

You can either:

¨        Upload a data source snapshot by clicking Upload File  then Browse  to browse to the file source.

After selecting the file, it is displayed with the timestamp of the snapshot.

To change the data source, click  then Browse  to browse to a new version of the file.

¨        Link to a data source file by clicking Link to File  and entering a Excel File Path.

Ensure that in a cluster, you need to use a a shared path, or put it on every node and use a path that resolves on every node. You can update its contents whenever you want.

·         Web URL

The dialog changes slightly to allow specification of the following:

Property	Description
Path	The absolute path including the http where the Excel file is located.
Proxy Server URI	The HTTP Proxy setting that will allow the Excel connector to reach the endpoint
Headers	·         Headers are separated by a comma ·         Each Header is entered as Name = Value, where Name and Value can be enclosed in double quotes to allow inclusion of any character except for double quotes ·         Name and Value can also be left unquoted, in which case they may not include comma or equals characters
Content Encoding	Select the Content Encoding with the HTTP Header: None, GZip, Deflate, or GZip and Deflate
User Id	The user Id that will be used to connect to the Excel service.
Password	The password to connect to the Excel service.
Http Method	Select the HTTP Method to map any of the following operations to HTTP requests ·         GET – retrieve information ·         POST – create or update an entity ·         PUT – replace an existing entity ·         DELETE – remove a request
Timeout	The length of time to wait for the server response (10 to 300). Default is 10.
Request Body	The Request Body for the HTTP POST.
Content Type	The required Content Type. Default is application/x-www-form-urlencoded

 

3.     Select the number of rows that will be skipped in the Excel file from the Skip First n Rows drop-down list.

4.     Click . This will populate the Sheet drop-down list box.

5.     Select the required sheet.

6.     Click  to save and display the details of the data extract.

7.     Click  then  to display the data preview.