eiConsole v.24R1
Tutorial & Interface

eiConsole – Advanced Topics

Using Transformers

Note: The Quick Start, Foundation, Topology and Data Mapping & Transformation tutorials should be completed before proceeding with the Data Mapping & Transformation Tutorials.

Overview

This interface and related tutorial demonstrate the use of Transformers in Route configurations. In this particular exercise, an existing Route (from “Training – Creating a Simple Route”) is modified to transform a Microsoft Excel worksheet to XML and then back to a Microsoft Excel format. The tutorial will cover the basic use of Formats and Transformers using the Microsoft Excel Transformer. This tutorial expands on content and concepts covered in the “Creating a Simple Route” tutorial. This exercise will create a Route that picks up a Microsoft Excel file from a directory, transforms it into XML, transforms it from XML back to Microsoft Excel, and delivers it to another directory.

Before You Begin

Using Transformers
Working Directory

Download the Using-Transformers-Working-Directory.zip file with the sample Working Directory and unzip it on your computer to a convenient place. In our case, it’s c:\Users\{USER _NAME}\PilotFish eiConsole Working Directories\Using Transformers where {USER_NAME} is the user’s name.

Open the eiConsole, browse to your Using Transformers directory, and open it. The fully configured Using Transformers – Example route is included in the Working Directory. Your Route File Management screen will open, as shown below.

Next, follow the tutorial and walk through it step-by-step. You may check your work against the provided Route (Sample Data).

Create a New Route in the eiConsole

We’ll begin by creating a new Route similar to that used in Creating a Simple Route. We recommend naming it Using Transformers to fit with the title of this exercise.

Double-click on the Using Transformers

Set up a Directory/File Listener. Enter the string ..\..\in in the polling directory field

The polling directory can be found in:

c:\Users\{USER _NAME}\PilotFish eiConsole Working Directories\Using Transformers\in

We’ll restrict the extension of files we pick up to xls, meaning we’ll only be picking up Microsoft Excel files

Set up a Directory\File Transport. Enter the string ..\..\out in the polling directory field.

The transport directory can be found in:

c:\Users\{USER _NAME}\PilotFish eiConsole Working Directories\Using Transformers\out

We’ll also be modifying the Transport to create files with an xls extension since we’ll be creating Microsoft Excel files as output:

Define Actual Transformations

Now we’ll need to define our actual transformations. Each Source and each Target may have its own associated transformation. Transformations are encapsulated in “Formats,” which contain a Transformation Module and an XSLT Transformation. Formats are reusable between Sources and Targets and exist independently of Routes. Therefore, a Format defined in Route A may be reused in Route B.

To create a new Format, we’ll start by selecting our Source Transform (third) stage:

On to the bottom half, in the center of the screen, is a “Format Profile” panel:

From here, you can select an existing Format, search through Formats, or add a new Format. The eiConsole automatically provides a default, unmodifiable Format called “Relay (System Format),” which is used by Source and Target Transform Stages if the user does not change it. We’ll want to create a new Format, so click “Add Format”:

This will raise a dialog where you are expected to provide a unique name for the Format. It’s generally a good practice for Formats to be named according to their transformation; typically something like “X to Y,” where “X” and “Y” are types. For example, our Source Transform will be converting Microsoft Excel worksheets into XML, so we’ll call ours “Excel to XML”.

Give Your Format a Unique Name

Once complete, click the “OK” button. The Format panel should now look like this:

You should ignore the other two tabs for now. Formats have a directionality dictated by their used location. For example, for a Source Transform, a Format’s left side is the “Transformation Module,” while the right side is the “XSLT Transformation.” The default (initial) configuration of a Format uses neither. This means that the Transformation Module is set to “No Transformation” and the XSLT Transformation has the “Use Direct Relay” option checked (meaning “skip the XSLT”).

We’ll want to select the Microsoft Excel transformer.

Select the Microsoft Excel Transformer

After selecting the Transformer, you’ll see the configuration panel change to reflect the available options:

Click through the available tabs and review the options present. The default configurations are sufficient for this exercise, though you should familiarize yourself with some of the other available functionality.

For our Source Transform, no other configuration is necessary. This Format will take a Microsoft Excel workbook and convert it into an intermediary XML format.

Configuring Our Target Transform

Next, we’ll want to configure our Target Transform. In this case, we’ll be going in the opposite direction from our intermediate XML back to Microsoft Excel. This is actually quite useless from a functional perspective, as the output will be more-or-less identical to the input. However, once we begin testing the Route, the value will become somewhat more apparent.

Select the Target Transform (fifth) stage:

Once again, we have our Format panel available to us. You could select the previously created Format from our drop-down, but we’ll go ahead and create a new Format. Click “Add Format” again. This time we’ll name our Format “XML to Excel”:

With the Format added (and selected), you’ll notice that the Target Transform configuration looks slightly different than the Source Transform:

Primarily, the directionality is reversed. First, we potentially go through an XSLT Transformation, then through a Transformation Module. Once again, select the Microsoft Excel transformer:

Most Transformers are inherently bi-directional without requiring additional configuration. The Microsoft Excel Transformer is one of these; just by having it selected in the Target Transform instead of the Source Transform will reverse its direction.

Review the available options again:

On the XML to Excel tab, choose if you want to include a header line based on the tags block of the XML data to the output file.

We’ve now configured our Route from end-to-end. It should look something like this:

Testing Our Route End-to-End

We’ll now test our Route end-to-end. For our sample file, we’ve created an Excel sheet like so:

Place People.xls into the polling directory. This file can be found in:

c:\Users\{USER _NAME}\PilotFish eiConsole Working Directories\Using Transformers\data

The polling directory is:

c:\Users\{USER _NAME}\PilotFish eiConsole Working Directories\Using Transformers\in

Switch to Testing Mode, and Execute Test.

Once the processing completes, we’ll view the output at three different stages. First, at the Listener, prior to going into the Source Transform.

View Your Output

In this case, our stage output will be the binary representation of the Excel worksheet:

Next, we’ll view it at the Routing stage, which is after it has undergone a transformation to XML:

Our output here is XML:

You should spend some time reviewing the output here and comparing it to the original sample. The XML created has a root tag called XCSExcelBook, and each sheet creates an XCSExcelSheet, which in turn contains a Columns tag (detailing each column defined) and then an XCSExcelRow tag for each row. Columns are then defined and named within each XCSExcelRow tag (for example, “Middle_Name”).

Finally, view the stage output at the Target Transform’s third stage:

We should see binary data again:

Finally, check your output directory…

c:\Users\{USER _NAME}\PilotFish eiConsole Working Directories\Using Transformers\out

…and view the resulting Microsoft Excel file – output.xls

This concludes the tutorial.