Microsoft Excel Translator

The Microsoft Excel Translator, known by the type name excel, exposes querying functionality to Excel document. Microsoft Excel is a popular spreadsheet software that is used by all the organizations across the globe for simple reporting purposes. This translator provides an easy way read a Excel spreadsheet and provide contents of the spreadsheet in the tabular form that can be integrated with other sources in Teiid.

Note
 "Does it only work on Windows?" - No, it works on all platforms, including Windows and Linux. This translator uses Apache POI libraries to access the Excel documents which are platform independent.

Usage

The below table describes how Excel translator interprets the data in Excel document into relational terms.

Excel Term Relational Term

Workbook

schema

Sheet

Table

Row

Row of data

Cell

Column Definition or Data of a column

Excel translator supports "source metadata" feature, where given Excel workbook, it can introspect and build the schema based on the Sheets defined inside it. There are options available for you guide, to be able to detect header columns and data columns in a work sheet to define the correct metadata of a table.

VDB Example

The below shows an example of a VDB, that shows a exposing a Excel Document.

CREATE DATABASE excelvdb;
USE DATABASE excelvdb;
CREATE SERVER connector FOREIGN DATA WRAPPER excel OPTIONS ("resource-name" 'java:/fileDS');
CREATE SCHEMA excel SERVER connector;
SET SCHEMA excel;
IMPORT FROM SERVER connector INTO excel OPTIONS (
    "importer.headerRowNumber" '1',
    "importer.ExcelFileName" 'names.xls');

As an XML VDB:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="excelvdb" version="1">
    <model name="excel">
        <property name="importer.headerRowNumber" value="1"/>
        <property name="importer.ExcelFileName" value="names.xls"/>
        <source name="connector" translator-name="excel"  connection-jndi-name="java:/fileDS"/>
    </model>
</vdb>

"connection-jndi-name" in above represents connection to Excel document.

Note
 "Headers in Document?" - If you have headers in the Excel document, you can guide the import process to select the cell headers as the column names in the table creation process. See "Import Properties" section below on defining the "import" properties.

Import Properties

Import properties guide the schema generation part during the deployment of the VDB. This can be used in a native import.

Property Name Description Default

importer.excelFileName

Defines the name of the Excel Document to import metadata. This can be defined as a file pattern (*.xls), however when defined as pattern all files must be of same format and the translator will choose an arbitrary file to import metadata from. Use file pattern to read data from multiple Excel documents in the same directory, in single file case choose the absolute name.

required

importer.headerRowNumber

Defines the cell header information to be used as column names

optional, default is first data row of sheet

importer.dataRowNumber

Defines the row number where the data rows start

optional, default is first data row of sheet

It is highly recommended that you define all the above importer properties, such that information inside the Excel Document is correctly interpreted.

Note
 Purely numerical cells in a column contain containing mixed types will have a string form matching their decimal representation, thus integral values will have .0 appended. If you need the exact text representation, then cell must be a string value which can be forced by putting a single quote ' in front of the numeric text of the cell, or by putting a single space in front of the numeric text.

Translator Extension Properties

Excel specific execution properties:

  • FormatStrings- Format non-string cell values in a string column according to the worksheet format. Defaults to false.

Metadata Extension Properties

Metadata Extension Properties are the properties that are defined on the schema artifacts like Table, Column, Procedure etc, to describe how the translator needs to interact or interpret with source systems. All the properties are defined with namespace 'http://www.teiid.org/translator/excel/2014[http://www.teiid.org/translator/excel/2014\]', which also has a recognized alias 'teiid_excel'.

Property Name Schema Item Property Belongs To Description Mandatory

FILE

Table

Defines Excel Document name or name pattern (*.xls). File pattern can be used to read data from multiple files.

Yes

FIRST_DATA_ROW_NUMBER

Table

Defines the row number where records start in the sheet (applies to every sheet)

optional

CELL_NUMBER

Column of Table

Defines cell number to use for reading data of particular column

Yes

The below shows an example table that is defined using the Extension Metadata Properties.

CREATE DATABASE excelvdb;
USE DATABASE excelvdb;
CREATE SERVER connector FOREIGN DATA WRAPPER excel OPTIONS ("resource-name" 'java:/fileDS');
CREATE SCHEMA excel SERVER connector;
SET SCHEMA excel;
CREATE FOREIGN TABLE Person (
                ROW_ID integer OPTIONS (SEARCHABLE 'All_Except_Like', "teiid_excel:CELL_NUMBER" 'ROW_ID'),
                FirstName string OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '1'),
                LastName string OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '2'),
                Age integer OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '3'),
                CONSTRAINT PK0 PRIMARY KEY(ROW_ID)
             ) OPTIONS ("NAMEINSOURCE" 'Sheet1',"teiid_excel:FILE" 'names.xlsx', "teiid_excel:FIRST_DATA_ROW_NUMBER" '2')

As an XML VDB:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="excelvdb" version="1">
    <model name="excel">
        <source name="connector" translator-name="excel"  connection-jndi-name="java:/fileDS"/>
         <metadata type="DDL"><![CDATA[
             CREATE FOREIGN TABLE Person (
                ROW_ID integer OPTIONS (SEARCHABLE 'All_Except_Like', "teiid_excel:CELL_NUMBER" 'ROW_ID'),
                FirstName string OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '1'),
                LastName string OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '2'),
                Age integer OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '3'),
                CONSTRAINT PK0 PRIMARY KEY(ROW_ID)
             ) OPTIONS ("NAMEINSOURCE" 'Sheet1',"teiid_excel:FILE" 'names.xlsx', "teiid_excel:FIRST_DATA_ROW_NUMBER" '2')
        ]]> </metadata>
    </model>
</vdb>
Note
 "Extended capabilities using ROW_ID column" - If you define column, that has extension metadata property "CELL_NUMBER" with value "ROW_ID", then that column value contains the row information from Excel document. You can mark this column as Primary Key. You can use this column in SELECT statements with a restrictive set of capabilities including: comparison predicates, IN predicates and LIMIT. All other columns can not be used as predicates in a query.
Tip
 User does not have to depend upon "source metadata" import to create the schema represented by Excel document, they can manually create a source table and add the appropriate extension properties to make a fully functional model. If you introspect the schema model created by the import, it would look like above.

With 10.3+ the Excel translator does support updates with a couple of limitations: * The ROW_ID can not be directly modified or used as an insert value. * Update and insert values must be literals. * Updates are not transactional - the write lock is only held while writing the file and not over the entire update, thus it is possible for one update to overwrite another.

The ROW_ID of an inserted row can be returned as a generated key.

JCA Resource Adapter

See File Data Source, the FTP Data Source and the Admin Guide in general for configuration information.

Native Queries

Note
 This feature is not applicable for the Excel translator.

Direct Query Procedure

Note
 This feature is not applicable for the Excel translator.

results matching ""

    No results matching ""