Log out?

eiConsole Advanced – Transformation / File Specification

eiConsole v.13.17R1
Tutorial & Interface

Handling Delimited Files

Overview

In this interface and related tutorial we’ll demonstrate the use of the File Specification Editor in Route configurations to convert between delimited Formats and XML. In this particular exercise, an existing Route (from “Training – Creating a Simple Route”) is modified to transform a delimited representation of People into a well-structure XML format. In this tutorial we’ll cover using the “Delimited and Fixed-Width File” Transformer and the related File Specification Editor to convert a delimited file into XML. This tutorial assumes you’re familiar with the concepts demonstrated in “Using Transformers” and extends them by introducing an additional Transformer.

How to Use This Example

Click the links below for the files you will need for this tutorial, then download the INTERFACE to get started. (Once logged into the eiConsole it immediately shows up in your “PIE” inbox for use.)

icon_sample-dataSample Data
icon_file-configInterface

Note: if you are not logged in to the eiConsole, or have arrived at this page via a browser, you will be prompted to login before the interface downloads into your eiConsole “PIE” inbox. Or if you have not downloaded and installed a FREE eiConsole Trial you will have the opportunity to register after you click the Interface Download link. Once you are logged in the interface will automatically download into your “PIE” inbox. First, you’ll see a screen pop up indicating that a new product is ready for download. Select the Download Now button. Once you’ve downloaded the Interface you can drag it into your current Working Directory and begin.

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

Create a new Route with a Directory/File Listener / Transport pair, configured in the same way as those in “Creating a Simple Route.” The one modification we’ll make is to change the Directory / File Transport’s extension to “xml”:

handling-delimited-files-01-13-16-r3

Add a Format

We’ll now need to add our Format. One important concept to note is that the direction of a transformer is determined by where it appears in the Route. If we configure our Transformer on the Target Transform stage then it will expect XML as input and convert that to the described delimited format. Conversely, configuring the Transformer on the Source Transform will assume delimited input and produce XML output.

Because we wish to convert from a delimited format to XML, we’ll want to add our Format on the Source Transform stage. Click that stage, then click Add Format:

handling-delimited-files-02-13-16-r3

We’ll name the Format based on the transform direction and the name of the structures. We’ll be using a sample called “people_delimited.txt,” which should have been provided with this tutorial, so the Format will be named “Delimited People to XML”:

Name the Format

handling-delimited-files-03-13-16-r3

With the Format added, the bottom half of the eiConsole screen changes to reflect the Format configuration. Once again, we’re interested in the Transformation Module. Selected “Delimited and Fixed-Width File” from the “Transformation Module” drop-down:

handling-delimited-files-04-13-16-r3

With the Delimited and Fixed-Width File Transformer selected, you should see a configuration panel like the following:

handling-delimited-files-05-13-16-r3

This particular Transformer makes use of an XML format informally called a “File Specification.” The configuration item specifies the name (and relative location) of such an XML description. If you had such an existing file, you could click “Browse” and select it, which would then copy it to the appropriate location. For this tutorial, we’ll assume that we’re starting fresh. Clicking the “Edit” button without any “File Specification” selected will open the “File Specification Editor;” do so:

handling-delimited-files-06-13-16-r3

The File Specification Editor is a graphical tool used for defining the structure of a file format. When first opened, you will be presented with a dialog asking you how you wish to begin:

handling-delimited-files-07-13-16-r2

The various options in this drop-down are ways to automatically build or import the structure of a File Specification. For example, you could read in definitions from a CSV file, a Cobol Copybook, or import various industry-specific formats (depending on what eiConsole version you have).

Building a File Specification from scratch

If you wish to build a File Specification from scratch, you’ll want to select “User-defined”:

handling-delimited-files-08-13-16-r2

Click “Next” and you’ll be presented with this dialog:

handling-delimited-files-09-13-16-r2

This dialog asks you to describe the style of Records and Fields. A File Specification is made up of Records with additional Records and Field as children. In most formats, Records are delimited, meaning that some character or set of characters separates each Record. In our “people_delimited.txt” sample, each line in the file represents a “Person,” which is a type of Record.

Select “Delimited” under “Record Style”:

handling-delimited-files-10-13-16-r2

The next item is “Record Delimiter,” which specifies what separates each Record. The default value, “\n,” represents a “line feed.” The convention used for escape characters is that used for Java (and most languages). Most formats use either a single line feed or a “carriage return” (\r) and a line feed (\r\n). Our sample file uses only a line feed, so set the Record Delimiter to “\n”:

handling-delimited-files-11-13-16-r2

Next we’ll need to define the style of Fields. In our sample file, each Field within a Record is separated by a pipe (“|”) character. Delimited files typically use tabs (“\t”), commas (“,”), pipes, and other such characters. Fields may also be “Fixed Width,” meaning that each field has a specific length. For example, the “First Name” field might be exactly 10 characters long.

Because our sample file is delimited by pipes, select “Delimited” and set the “Field Delimiter” to “|”:

handling-delimited-files-12-13-16-r2

Finally, click the “Save” button to open the main File Specification Editor window:

Reviewing the File Specification Editor

handling-delimited-files-13-13-16-r1

There are a lot of panels and areas for the File Specification Editor, but we’ll review each of these in turn to explain them.

On the left side of the screen is the “Record Structure” panel:

media_1442249347807.png

This panel contains the structure (so far, it’s empty) of the File Specification, defined as a hierarchy (a tree) of Records, sub-Records, and Fields. The bottom half of the panel is used to provide information and configurations for the selected Record or Field.

The top-right panel is used to show currently loaded sample data with highlighting for a selected Record:

media_1442249416031.png

Until we have a structure defined and a sample file loaded, this will be empty and fairly uninteresting.

Finally, the “Results Preview” panel:

handling-delimited-files-16-13-16-r1

This panel is used to load and / or edit a sample file, transform it, and see the results. We’ll start here. Click the “Load” icon and select “people_delimited.txt”:

handling-delimited-files-17-13-16-r1

With the sample file loaded, the sample file panel should resemble the following:

handling-delimited-files-18-13-16-r1

Defining our Structure

We’ll now need to define our structure. We can start by defining a Record to represent each “Person” in our sample file. Right-click on the root Node (labeled “Base”) and select “Add New Record”:

handling-delimited-files-19-13-16-r3

You will next be presented with a dialog where you are expected to name the new Record. Since this Record will represent a Person, we’ll name it accordingly:

media_1442249691653.png

Click the “OK” button and you should see the “Person” record shown in the structure underneath “Base”:

media_1442249733570.png

We’ll now wish to start adding Fields to the Person Record. To do so, right-click on Person and select “Add New Field”:

Adding Fields to the Person Record

We’ll now wish to start adding Fields to the Person Record. To do so, right-click on Person and select “Add New Field”:

handling-delimited-files-22-13-16-r2

Once again, a dialog will be raised asking you to name the Field. Our first Field is the Person’s first name, so we’ll name the Field “First Name”:

media_1442249870946.png

Click the “OK” button and you should see the Field displayed under Person:

media_1442249913024.png

For defining the remaining Fields, you could continue to right-click on “Person” and select “Add New Field” or, with the “First-Name” field selecting, simply hit “Enter / Return” on your keyboard. The next field is “Middle Name.”

Continue adding Fields for each of the following:

  • First Name
  • Middle Name
  • Last Name
  • Gender
  • Social Security Number
  • Date of Birth
  • Occupation
  • Vehicle Make
  • Vehicle Model
  • Address Line 1
  • Address Line 2
  • City
  • State
  • Zip

When you are finished, the structure should look like this:

media_1442249971623.png

Our structure is now fully defined. Next, select the Person Record and then highlight a single line of the sample file (this might be easier if you click at the beginning of that line, hold Shift, and hit End):

handling-delimited-files-26-13-16-r1

Next, click “Set Selected Text”:

handling-delimited-files-27-13-16-r1

You’ll notice the top panel now has various fields shown, as well as blue arrows:

media_1442250164382.png

If you click on the various Fields in the structure, the appropriate field will be highlighted in this panel. For example, if we click Occupation, we’ll see “Long distance operator” highlighted:

media_1442250213516.png

This panel allows you to visually confirm that your structure matches your sample (or vice-versa). While this is less important in delimited files, it can be an enormous time saver when dealing with fixed-width formats.

Testing our Definition

Next, we’ll want to test our definition. Click the “Execute Transform to XML” button:

handling-delimited-files-30-13-16-r1

You should see various Person elements appear in the results panel:

media_1442250357817.png

If you click to the left of these elements, it will expand them to show their respective child elements and values. For example, the first Person element should resemble this:

media_1442250401924.png

If you click the “XML” tab, you can also view how this would look in the resulting XML.

Our File Specification is now fully defined. One interesting aspect to note is that this specification will convert the “people delimited” format to XML or the resulting XML format back to the delimited format without any additional configuration or changes; File Specifications are inherently bi-directional.

Save your specification with the “Save” button.

media_1442250458517.png

Provide any name you feel appropriate (we’ll use “Delimited People”):

media_1442250479685.png

Finally, close the Editor to return to the eiConsole. Our Format configuration should now look like this:

handling-delimited-files-35-13-16-r3

Testing

With that, we’ve now completely configured our Route, as well. Switch to Testing Mode, place the sample file (“people_delimited.txt”) into the input directory, and let the test run all the way through. Select the Listener stage and view the stage output to see the format in it’s “before” stage:

handling-delimited-files-36-13-16-r3

As you can see in the Stage Output Viewer, the file picked up matches our expected, delimited format:

handling-delimited-files-37-13-16-r1

Next, select the Routing stage (since it occurs after our transformation) and view its output:

handling-delimited-files-38-13-16-r3

You should see a nice, well-structured XML document:

handling-delimited-files-39-13-16-r3

You can compare the format before-and-after if you like, but that’s pretty much all that’s necessary to freely convert between delimited and XML formats.

You have now concluded this tutorial.

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

Thanks!

Our editors are notified.

Close