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:

SQL

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 `column1`, `column2`... `columnN` (N is the total number of columns in the MS Excel file).
`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: `AUTO` - the Event API will be used only for files with tthe .xlsx extension; `TRUE` - the Event API is always turned on regardless of file extension (will cause errors when used with files other than .xslx); `FALSE` - the Event API is turned off
`importer.rowCacheSize`	`100`	int	Optional	Number of rows to keep in memory; will be used only if the `importer.useEventApi` property is set to `AUTO` or `TRUE`
`importer.bufferSize`	`4096`	int	Optional	Buffer size to use when reading `InputStream` to file; will be used only if the `importer.useEventApi` property is set to `AUTO` or `TRUE`
`importer.allColumnsAsString`	`FALSE`	String	no	In some cases, the Excel translator does not correctly detect the right data types for each column. When `allColumnsAsString` is set to `TRUE`, all columns will be treated as a string and might be manually cast to the right data type in the CData Virtuality Server. Translator property `formatString` should be used together with `allColumnsAsString`. If `allColumnsAsString` is set to `TRUE`, `formatString` is set to `TRUE` by default. This causes that values are formatted as it looks in Excel sheet. Values are shown in digital representation If `allColumnsAsString` is set to `TRUE` and `formatString` is set to FALSE. For example 2023-05-04 data value is shown as 45050.0