Red Hat Training

A Red Hat training course is available for Red Hat JBoss Data Virtualization

12.22. Microsoft Excel Translator

The Microsoft Excel Translator, known by the type name excel, exposes querying functionality to Excel documents using File Data Sources. This translator provides an easy way to read an Excel spreadsheet and provide the contents of the spreadsheet in a tabular form that can be integrated with other sources in JDV.

Note

This translator works on all platforms, including Micrsofot Windows and Linux.
This table describes how the translator converts the data in Excel documents:

Table 12.17. Translation

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 to detect header columns and data columns in a work sheet to define the correct metadata of a table.
Here is an example of Dynamic VDB, that shows you how to expose an Excel spreadsheet: 
<?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 the code sample above represents the connection to the Excel document. The Excel translator does NOT provide a connection to the Excel Document. For that purpose, Teiid uses File JCA adapter that provides a connection to Excel. To define such connector, see File Data Sources or see an example in jboss-as/docs/teiid/datasources/file. Once you configure both of the above, you can deploy them to Teiid Server and access the Excel Document using either the JDB, ODBC or OData protocol.
If you are using Designer Tooling, to create Excel based VDB, use a Teiid Designer Model project. Use "Teiid Connection - Source Model" importer, create File Data Source using data source creation wizard and use excel as translator in the importer. Based on the Excel document relevant relational tables will be created. Create a VDB and deploy into Teiid Server and and access the Excel Document using JDBC/ODBC/OData protocol.

Note

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 guide the schema generation part during the deployment of the VDB. This can be used in Dynamic VDBs or while using "Teiid Connection >> Source Model" in Teiid Designer.

Table 12.18. Import Properties

Property Description Default
importer.excelFileName
Defines the name of the Excel Document
required
importer.headerRowNumber
optional, default is first data row of sheet
required
importer.dataRowNumber
optional, default is first data row of sheet
required

Note

Red Hat recommends that you define all the above importer properties, so that information inside the Excel document is correctly interpreted.

Note

Purely numerical cells in a column 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 the cell must be a string value (you can force it to be this 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.)
Currently there are no Translator Extension properties defined for this translator.
Metadata Extension Properties are the properties that are defined on the schema artifacts like Table, Column, Procedure to describe how the translator interacts with source systems. All the properties are defined with namespace "{http://www.teiid.org/translator/excel/2014\}", which also has a recognized alias "teiid_excel".

Table 12.19. Metadata Extension Properties

Property Schema Item Description Mandatory?
FILE
Table
Defines Excel Document name or name pattern
yes
FIRST_DATA_ROW_NUMBER
Table
Defines the row number where records start
Optional
CELL_NUMBER
Column of Table
Defines cell number to use for reading data of particular column
Yes
Here is an example table that is defined using the Extension Metadata Properties: 
<?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.

Note

The user does not have to depend upon "source metadata" import, or Designer tool 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 the code above.
There is no Teiid-specific Excel Resource Adapter. Use the File JCA adapter with this translator.
The Excel translator does not yet support updates.