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: "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: 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.

Commands:
<?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>
<?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>