Getting started with Stambia's JDBC Excel Driver

This article demonstrates how to create metadata and mappings for reading / writing Excel files (XLSX), using Stambia DI's Excel Driver.

We are going to use the Demo database from the Stambia Tutorial.

Preparation

Create an excel file named "customers.xlsx" with the following columns.

 

Note: Stambia DI's Excel driver supports "XLSX" file format, not "XLS" file format.

 

Reversing the Excel File

Create a metadata of type "rdbms/Microsoft Excel DataServer". Name it "Excel" for instance.

 

Note the columnNameStyle parameter, which can be one of the following :

• PRESERVE: the column names are used as specified in the metadata
• NORMALIZE: removes the special characters from the column names, and make them UPPERCASE.
• FLAT: removes special characters from the column name, and make them lowercase.

 

Click on "Connect" and "Next".

On the Schema wizard, select the file name in the Catalog list, and then click Next :

 

You can see the sheets are listed as datastores (just like tables). Select the sheet you want to use and click Finish.

Your Excel sheet is reversed into Stambia:

 

Creating a mapping to write into a sheet

Create a mapping and just drag and drop the Sheet as a target.

Then drag and drop the T_CUSTOMER and T_TITLE tables from the Tutorial Metadata. Join them on the TIT_CODE column.

Important: add a "NO_LOAD" tag. The Excel mappings directly Integrate data into the target, they do not support Load features. Transformations can be applied to the source, to a transient table, or to a Stage (since version S18). 

• Stambia Designer S17.x: in the mapping's "Properties", using the "Criterias" field and "+" button.
• Stambia Designer S18.x: in the target's "Properties", using the "Criterias" field and "+" button.

 

 

On the "Integration" template, make sure you enable "Append Mode". 

Optionnally you can check "Truncate target table" if you want to empty the Excel sheet before integration.

Note:

• the "Do Update" property has no effect, it is currently not possible to update existing data in a sheet.
• the template has a "Order by expression" property, which can be used to sort data from the source.

 

 

Execute the mapping.

Your Excel file is filled with customer data from the database.

 

Note: you can right click on the Excel sheet's metadata node, and choose "Consult data".

 

Creating a mapping to read from the Excel file

Create a mapping with a target table that has the following structure :

Create table HOTEL_MANAGEMENT.TEST_CUSTOMER
( 
 CUS_ID INTEGER NOT NULL,
 TIT_NAME CHAR(8) ,
 CUS_LAST_NAME CHAR(32) NOT NULL,
 CUS_FIRST_NAME VARCHAR(25) 
)

Drag and drop the excel sheet on the mapping. Make sure the Excel fields are mapped to their corresponding columns. 

You can now execute this mapping and the data will be loaded from the Excel file to the TEST_CUSTOMER table.