Skip to main content
Skip table of contents

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 nameDefault valueTypeMandatory/OptionalDescription
importer.excelFileName
StringMandatoryThe MS Excel file name used for getting data rows.
importer.headerRowNumber
intOptional

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

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

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

Number of rows to keep in memory; will be used only if the importer.useEventApi property is set to AUTO or TRUE

importer.bufferSize4096intOptional

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

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 TRUEformatString 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

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.