Log out?

7. Source Transformation / Data Mapping (Tutorial)

eiConsole v.22R1
Tutorial 90 mins

eiConsole Foundation

Source Transformation/Data Mapping

You will need to download sample files to do this tutorial. If you have not done so yet, please click this LINK.

Note: The Tutorials in eiConsole Foundation, 1-13, are modular and are designed to be used in the sequence presented.

Click the data mapping icon to get started in the eiConsole.

Next, the eiConsole’s Data Mapper opens. We’ll use the Data Mapper to define the logical mapping between the flat file source and the XML standard target. When you open the Data Mapper, you will see 3 panels. The panel on the left represents the logical structure of the source data. The panel on the right represents the logical structure of the target data. The panel in the middle is used to generate the logical mapping between the two.

To load in the Source format, choose the Open source format icon.


Select the source file format in the data mapper.

Select the Flat File and double-click. This will allow you to leverage the file specification created in the File Spec Editor to populate the tree on the left-hand side of the screen.


Click Add for the Source File Format selected.

When the Select Format dialogue opens, click Add.


Save the source file format selected in the data mapper to the format folder.

Navigate to your Getting-Started-Project Working Directory.


Select the format file format for the data mapper.

Select the formats folder. Underneath that folder, you will find the FlatFileToXML folder.

Select the FlatFileToXML folder and double-click to open. This contains the file specification that you created in the previous stage. Select FlatFileSpec.xml and click Open.


Select the file specification file created previously.

When the Select Format dialogue opens, click Read Format.


The left-hand side of the tree (the Source System) is populated with the logical structure of your inbound tab-delimited file.

To expand the tree, double-click on the Record node.

Choose the Open Target format icon on the right to do the same with the Target format.


Select the file format to read into the data mapper.

This time, read the Target format from an XML schema. Then, in the Select Format dialogue from the drop-down menu, select and double-click the XSD (Schema).


Read the data target format from an XML Schema file.

When the Select format dialog opens, you will be prompted to select a schema file. Click Browse.


Select the Schema file from the drop-down.

Navigate to c:\Program Files\PilotFish Technology\Samples\data\ folder or the location of the sample folder of your distribution. Select it and double-click to open. Next, double-click the data folder.


Open the sample data file to be used in the eiConsole tutorial.

The data folder will include an XML schema that describes a standard mechanism for expressing a photo album as XML – the photo-album.xsd file. Select the file and then click Open.


Read the Schema File format into the Data Mapper.

When the Select Format dialogue opens again, click Read Format.


The tree on the right represents the logical XML structure of the output file.

The tree on the right now represents the logical structure of the XML format that you would like to output.

Double-click the nodes including Picture+, Photographer and LocationTaken to fully expand the tree. You’ll need the tree fully expanded to do your mapping.


Click the nodes to expand the detail in the records.

You’ll now create the mapping between the two. To start this mapping, select the “Mapping” tab in the Data Mapper, then you’ll need to create the structural format of the output. To do this, you want to create one logical structure for each inbound Data Element, which is the root element of the XMLized flat data.

To begin, drag & drop the XCSData element (Source, left column) onto the stylesheet XSLT element (center panel). The blue XCSData element will now appear below the stylesheet element, as shown above.

Note: The red V that appears on your node, indicating a mapped element.

Note: You’ll want to drag & drop your element on top of the style sheet element so that you see the gray bar above it and then release it. If you make a mistake, you can delete mapping elements by right-clicking and selecting “Delete” from the context menu.


Drag & Drop fields into the data mapper central panel.

At the root of the output (green elements in the right panel), select the PhotoAlbum tag. Then, drag & drop this tag onto the XCSData element in the middle panel.


Drag & Drop fields into the data mapper central panel.

You will also want to add a Title. Drag & drop the Title element from the right column (target) onto the PhotoAlbum element in the middle panel. When done, it should look like the above mapping.


Data Mapping Route Interface eiConsole

Now you will have a number of records in the inbound file, each of which needs to be mapped to a picture element in the XML output. To handle this looping structure, use the XSLT palette (above the main mapping panel).

Select the Flow Control tab in the XSLT palette. Next, drag & drop the for-each element onto the PhotoAlbum element. You’ll now see the blue for-each element added to the mapping.

Note: The XSLT palette of structures and functions at the top allows you to do anything you can do by programming via the eiConsole’s drag & drop process.


Data mapper examples of drag n drop fields from the source.

Next, you will want to iterate over each inbound record. Select Record from the source and drag & drop it onto the [@select] element.

Note: Elements are placed at valid locations automatically when mapped.


Drag fields onto the center panel to set up looping structure.

For each Record, you will want to generate a Picture element. Drag & drop the green Picture+ element from the tree on the right panel onto the blue for-each element in the middle panel.


Drag fields onto the center panel to set up looping structure.

For each Picture, include a Title, FileName and Photographer element. Drag & drop Title, FileName, and Photographer from the right panel onto the Picture element.

Note: You can do this one at a time or as a group.


Data Mapping Route Interface eiConsole

Underneath Photographer, you will want to include the FirstName and LastName. You can drag & drop these elements individually or together onto Photographer.


Drag & drop fields from the panel on the right in the data mapper.

You will also include some aspects of the address of the Photographer. Drag & drop Address onto Photographer. For the next mapping, you will need to expand this Address node.


Drag & drop fields from the panel on the right in the data mapper.

Then drag & drop Address 1, City and State onto Address. When finished, all will show red Vs on the nodes. Your mapping should now look like the above screen.


Drag & drop fields from the panel on the right in the data mapper.

Next, include 2 phone numbers for the Photographer: home and work. Drag & drop PhoneNumber onto Photographer twice. Notice that they appear at the end of your mapping.


Drag & drop fields from the panel on the right in the data mapper.

Next, drag & drop the type attribute (found under PhoneNumber in the target format) on top of both PhoneNumbers in the mapping. To view the Type attribute, you will need to expand the target tree by clicking the PhoneNumber node. Your mapping should now look like the mapping above.


Drag & drop fields from the panel on the right in the data mapper.

You will also want to add information about where the photograph was taken. Drag & drop the LocationTaken element onto Picture. Notice the element is placed automatically at the valid location, in this instance under the Type element at the very bottom of the mapping.


Drag & drop fields from the panel on the right in the data mapper.

Then drop City and Country (from under LocationTaken in the target format) to LocationTaken in the mapping.


Associate source fields with target data fields in the data mapper example.

You have now created the structure that you will populate from values in the source. The next step is to associate source fields with target data elements.

Click the Record node in the Source panel to expand the tree. The first information to map falls under Picture in the mapping (center panel). Next, you will map Photo-Name to Title. Then drag & drop Photo-Name from the source format (left panel) onto Title in the mapping. You will see the blue element appear underneath the green Title and blue Photo-Name elements.

Create a similar association between File-Name in the target and File-Name in the mapping.


Associate source fields with target data fields in the data mapper example.

From the Source, drag & drop Photographer-First on top of FirstName (under Photographer), then drag & drop Photographer-Last on top of LastName (under Photographer) in the center panel.


Associate source fields with target data fields in the data mapper example.

From the source, drag & drop Address on top of Address1 in the mapping. Also, from the source, drag & drop City on top of City, and State on top of State.


Associate source fields with target data fields in the data mapper example.

There are 2 phone number elements to map: Home and Work. First, you will need to scroll down to the bottom of the center panel to access the elements. Next, drag & drop the Home-Phone element from the source to the first PhoneNumber in the map and the Work-Phone element from the source to the second PhoneNumber in the map. Your mapping should now look like the above.


Associate source fields with target data fields in the data mapper example.

For LocationTaken in the mapping, map Dive-Site from the source to the City and Dive-Country from the source to the Country. Leave Dive-Depth unmapped. (You may have to scroll to have access to these elements.) Your mapping should now look like the image above when you are done.


Clarify the meaning of fields in the data mapper.

You have now mapped values from the source onto values from the target. Several text constants need to be added to clarify the meaning of various fields. For example, Title is unmapped. Scroll up in the center panel—Right-click on Title and select Add Text Constant from the drop-down menu.


Set identified field to a constant value in the data mapper.

When the Please set constant value dialogue panel opens, enter “My Photo Album” and click OK.


Identify the attribute type in the data mapper.

Now, scroll down to the PhoneNumber section of the mapping. You will see the type attribute, which indicates the type of phone number that each PhoneNumber element represents. Double-click the first PhoneNumber type attribute. Next, in the Value text box, enter 'H' to represent Home (use caps and make sure you use single quotes) and hit enter/return.

Do the same for the Work PhoneNumber. In the character box enter 'W' and hit enter/return.


Identify the attribute type in the data mapper.

When you are done, your mapping should look like the above.

Your logical data map is now complete. Hit the Save icon at the top.


Name the data mapping and save it to a named file.

When the Save File dialogue opens enter “PhotoAlbumFlatXMLToStandardXML” and click OK.

Now you are ready to move on to the next step—the Route Stage. Next, click the Return to Console icon at the very top.

This is a unique website which will require a more modern browser to work! Please upgrade today!