MS Excel (xlsx) Connector

The MS Excel (xlsx) connector allows for retrieval and processing of MS Excel workbook data stored using the XLSX file format. The MS Excel (xlsx) connector processes data on a row-by-row basis, allowing for better performance and less memory consumption compared to the MS Excel (legacy) connector.

Steps:

1.     Select MS Excel (xlsx)  from the Connectors panel. The MS Excel (xlsx) Settings panel displays.

 

2.     Enter the Name of the MS Excel (xlsx) data source, then click .

3.     Select the MS Excel (xlsx) file source. Do one of the following:

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

The data source is placed in the repository and locked, synchronized, and bundled with the workbook version.

To change the data source, click  then Browse  to browse to a new version of the file, which is uploaded into the repository, and also create a new version of the workbook that reads it.

·         Link to a XLSX data source file by clicking Link to File  and entering a File Path.

Ensure that in a cluster, you need to use 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. 

When a file is selected, the MS Excel (xlsx) connector will automatically select the first available sheet, and populate available columns.

4.     Adjust Sheet selection, if required. Selecting a new sheet will re-populate the Columns list.

5.     Adjust the Headers On First Row if needed. By default the connector will pick up headers from the first row if all cells on the first row contain text data.

You can opt to select one of the following:

·         Leave headers on first row as Auto if you want the connector to automatically pick up column names from sheet.

·         Select Yes to force picking first row as headers.

·         Select No to force not picking first row as headers. This will auto generate all column names.

 

6.     Adjust column Type or Date Format to adjust data interpretation.

7.     Set the row limit of the data set, if required.

8.     Tap the Preview Selected Data Source slider to turn it on.