Altair® Panopticon

 

Connector for MS Excel

This is the most commonly used data connector when prototyping and is 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.

The MS Excel connector supports reading data files stored in either the legacy XLS, and the newer XLSX format. The XLSX format is read on a row-by-row basis, allowing for better performance and less memory consumption compared to the XLS format.

   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.

 

Steps:

1.     Select the MS Excel 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 create a new version of the workbook that reads it.

 

·         Link to a MS Excel 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 connector will automatically select the first available sheet, and populate available columns.

 

By default, all the generated columns are enabled. You can unselect the Select All checkbox, then select the checkboxes of the columns that will be enabled.  

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

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

 

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