Microsoft Excel
The Microsoft Excel connector, known by the type name excel, exposes access to Microsoft Excel files. It supports two different loading methods: with and without the usage of Event Api (note that Event Api only works for *.xlsx file format with no MS Excel function inside).
Connector Configuration
Metadata
Before issuing queries to Excel files, we need to configure a connection using the SYSADMIN.createConnection()
and SYSADMIN.createDataSource()
procedures:
CALL SYSADMIN.createConnection(
name => 'excel',
jbossCLITemplateName => 'excel',
connectionOrResourceAdapterProperties => 'ParentDirectory=d:/tmp'
) ;;
CALL SYSADMIN.createDataSource(
name => 'excel', translator => 'excel',
modelProperties => 'importer.useFullSchemaName=false, importer.headerRowNumber=1, importer.ExcelFileName=names.xlsx', translatorProperties => ''
) ;;
Model Properties
The MS Excel connector supports the following model properties provided through the importer
prefix:
To view the full table, click the expand button in its top right corner
Property name | Default value | Type | Mandatory/Optional | Description |
---|---|---|---|---|
importer.excelFileName | String | Mandatory | The MS Excel file name used for getting data rows. | |
importer.headerRowNumber | int | Optional | The header row index in the MS Excel file for getting column names in the CData Virtuality Server. If the property is not set, the column names will be set as | |
importer.dataRowNumber | 0 | int | Optional | The data row index in the MS Excel file to start reading data rows from. if the property is not set, reading data rows from the MS Excel file will be started after the header row. The first data row will be used to get column types in the CData Virtuality Server. |
importer.useEventApi | AUTO | String | Optional | Indicated whether the Event API approach is used. Can have one of the following values:
|
importer.rowCacheSize | 100 | int | Optional | Number of rows to keep in memory; will be used only if the |
importer.bufferSize | 4096 | int | Optional | Buffer size to use when reading |
importer.allColumnsAsString | FALSE | String | no | In some cases, the Excel translator does not correctly detect the right data types for each column. When |