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');
Microsoft Excel translator
The Microsoft Excel Translator, known by the type name excel, exposes querying functionality to a Microsoft Excel document. This translator provides an easy way read a Excel spreadsheet and provide the contents of the spreadsheet in a tabular form that can be integrated with other sources in Teiid.
Note
|
This translator works on all platforms, including Windows and Linux. The translator uses Apache POI libraries to access the Excel documents which are platform independent. |
The following 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 |
The Excel translator provides a "source metadata" feature, where for a given Excel workbook, it can introspect and build the schema based on the worksheets that are defined within it. There are options available to detect header columns and data columns in a worksheet to define the correct metadata of a table.
The following example shows how to expose an Excel spreadsheet in a virtual database.
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 the preceding example represents the connection to the Excel document.
If the Excel document contains headers, you can guide the import process to select the cell headers as the column names in the table creation process. For information about defining import properties, see the following table, and also see Importer Properties in JDBC translators.
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 patterns to read data from multiple Excel documents in the same directory. In the case of a single file, specify 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. |
To enable information in the Excel spreadsheet to be interpreted correctly, it is best to define all the preceding importer properties.
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 the cell must be a string value.
You can force a string value by preceding the numeric text of a cell with a single quote (' ), or a single space.
|
-
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
Properties that are defined on schema artifacts, such as Table, Column, Procedure and so forth. These properties describe how the translator interacts with or interprets source systems. All the properties are defined with the following namespace:
"http://www.teiid.org/translator/excel/2014[http://www.teiid.org/translator/excel/2014\]"
, which also has a recognized aliasteiid_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 following example shows a table that is defined by 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>
If you define a 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 cannot be used as predicates in a query.
Tip
|
Importing source metadata is not the only way to create the schema of an Excel document. You can also create a source table manually, and then add the extension properties that you need to create a fully functional model. Metadata imports result in schema models similar to the one in the preceding example. |
The Excel translator processes updates with the following limitations:
-
The
ROW_ID
cannot be directly modified or used as an insert value. -
UPDATE and INSERT values must be literals.
-
UPDATEs are not transactional. That is, the write lock is held while the file is written, but not throughout the entire update. As a result, it is possible for one update to overwrite another.
The ROW_ID
of an inserted row can be returned as a generated key.
See File Data Source, the FTP Data Source and the Admin Guide in general for configuration information.
Note
|
Native queries
This feature is not applicable for the Excel translator.
|
Note
|
Direct query procedure
This feature is not applicable for the Excel translator.
|