Welcome Guest! Log in
Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.

The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…


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.

01excel

 

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.

02wizard

 

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 :

03schema

 

04datastore

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:

05reverse

 

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.

 

06tag

 

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.

07template

 

08map

 

Execute the mapping.

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

09result

 

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.

10mapread


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

 

 

Articles

Suggest a new Article!