The Microsoft Excel Translator, known by the type name excel, exposes querying functionality to Excel documents using File Data Sources. 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.
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.
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.
The below shows example of Dynamic VDB, that shows a exposing a Excel Document.
<?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. 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 JDBC/ODBC/OData protocol.
If you are using Designer Tooling, to create Excel based VDB
Create/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.
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.
|
Property Name |
Description |
Default |
|
importer.excelFileName |
Defines the name of the Excel Document |
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.
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 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\}", which also has a recognized alias "teiid_excel".
|
Property Name |
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 |
The below shows 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>
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.
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 above.
Currently Excel translator does not support updates.
The Teiid specific Excel Resource Adapter does not exist, user should use File JCA adapter with this translator. See File Data Sources for opening a File based connection.
This feature is not applicable for Excel translator.
This feature is not applicable for Excel translator.