Data Tools only work for Windows XP. Click Here for a solution

Jul 28

Written by:
7/28/2011 7:53 AM 

Question:

I have a source text file that contains two fields/columns. In the first column there are product SKUs, each of which is repeated on multiple rows. The second column contains the SIZEs of each of the products that I have in stock. For example, if I have 6 different SIZEs of one SKU, there are 6 rows for that SKU in my source file. How can I combine the data into a target text file that contains just one row for each SKU and each of the available SIZEs on that same row and in size order and separated by a vertical bar?

The format of the source file looks like this:

""SKU"",""SIZE""

""1111A"",8

""1111A"",6

""1111A"",16

""1111A"",12

""1112B"",13

""1112B"",8

""1112B"",5

""1112B"",16

""1112B"",12

""1113C"",6

""1113C"",8

""1113C"",14

I would like the format of the target file to look like this:

1111A 6|8|12|16

1112B 5|8|12|13|16

1113C 6|8|14

Answer:

Using the Data Loader to CSV Text or the Data Loader to Fixed Text, you can transform the format of the data exactly as you have described and illustrated.

Objective

There is one objective in this sample transformation map, as follows:

• Use the Rapid Integration Flow Language (RIFL) to write expressions in a variety of Event Handlers to transform the data format from the source file format to the desired target file format.

Skill Level

• Intermediate

Skill Set

• Map Designer
• Event Handlers
• RIFL functions and scripting
• Basic concepts and usage of variables

Design Considerations

The principal design consideration is to understand the precedence of the event handlers and event actions in the Map Designer and to utilize the flexibility and extensibility of event handling and the RIFL expression language to accomplish the desired transformation.

The DataTools product used in this sample transformation map is the Data Loader to CSV Text. (Note that the Data Loader to Fixed Text may also be used, but would require a few changes to the map design.)

Procedure

This is an outline of the procedure that is described in this sample transformation map. Details can be found in the sections that follow.

1. Launch the DataTools Data Loader to CSV Text.
2. Create a new transformation map.
3. Select the source and target connectors (see above).
4. Connect to the source and target data files.
5. Set source and target properties options.
6. Enter the data field mapping and event handling RIFL expressions.

Source Connection

The Source Connection tab is where you specify the source connector, select the source data file, and specify any special properties about your source data file. For this sample select the following:

• Source Connector: ASCII (Delimited)
• Source File/URI: SRC_Vertical_to_Horizontal_Orientation.csv
• Description: The source is a simple CSV text file with sample data containing SKUs and SIZEs as described and illustrated in the ""Question"" section above. NOTE: The sample CSV text file was not installed with DataTools. You can request a copy of the sample source file by posting a request in the user forum for the Data Loaders. Once you obtain the sample source file, save it to any folder to which you have access/permissions.

Source Properties

The source properties options provide numerous settings that tell the Map Designer how to correctly read and auto-parse the source data file. Source properties options that need to be changed from their default values as as follows. Leave the other source properties options' default settings for this sample map.

• Header: True

Click on the Apply button below the Properties Options grid to save the change.

Source File/Data Sorting

In order to write the data records to the target file correctly, the source records need to be sorted, at a minimum, by the SKU values. If you wish to write the SIZE values to the target in numerical order, the SIZE column must also be sorted. The following settings assume you wish to sort the data on both SKU and SIZE.

1. After completing the source connection, locate and click on the ""Source Keys and Sorting"" button in the button bar.
2. Click in the top row of the Key Expression cell.
3. Click on the down arrow and select the source data field labeled ""SKU"".
4. Leave ""Ascending"" as the default sort order.
5. Leave ""Text"" as the default Data Type.
6. Enter ""15"" (without quotation marks) in the Length cell.
7. Click in the second row of the Key Expression cell.
8. Click on the down arrow and select the source data field labeled ""SIZE"".
9. Leave ""Ascending"" as the default sort order.
10. Click in the Data Type cell, scroll down, and select ""Short Integer"" as the Data Type.
11. Verify that the value ""2"" (without quotation marks) displays in the Length cell.
12. Click on the OK button to save the sorting settings and to close the Source Keys and Sorting window.

Target Connection

The Target Connection tab is where you specify the target connector, enter the location and filename of the target data file, and specify any special properties about your target data file. For this sample select the following:

• Target Connector: ASCII (Delimited)
• Output Mode: Replace File/Table
• Target File: target.csv
• Description: The target is a CSV text file with one field/column. You may write the target file to any folder to which you have access/permissions.

Target Properties

The target properties options provide numerous settings that tell the Map Designer how to write some of the schema options in the target data file. Target properties options that need to be changed from their default values as as follows. Leave the other target properties options' default settings for this sample map.

• FieldSeparator: None
• FieldStartDelimiter: None
• FieldEndDelimiter: None

Click on the Apply button below the Properties Options grid to save the changes.

Transformation and Map Properties

This is where you will declare variables that are needed to collect and store data when the map is executed. In this sample, we need to declare two variables, as follows:

1. Locate and click on the ""Transformation and Map Properties"" button in the button bar.
2. In the tree on the left side of the window, locate and click on the property labeled ""Global Variables"" under the Map Properties node.
3. In the right side of the window, enter the letter ""a"" (without quotation marks) in the top row under the Name column.
4. Click in the Type column for the variable ""a"", scroll down, and select ""Variant"".
5. Do NOT check the option labeled ""Public"" in the third column.
6. In the right side of the window, enter the letter ""b"" (without quotation marks) in the top row under the Name column.
7. Click in the Type column for the variable ""a"", scroll down, and select ""Variant"".
8. Do NOT check the option labeled ""Public"" in the third column.
9. Click the OK button to save the variables you just declared and close the Transformation and Map Properties window.

Data Field Mapping and Expressions

This is where you create the schema of the target data file and one of the many places where you can write expressions to transform data on the fly when the transformation map is executed. In this sample there will be only one target field when the target file is created.

1. On the Map Fields tab, create the target schema by typing ""Field1"" (without the quotation marks) into the topmost empty cell of the Target Field Name column in the target grid on the right side of the window.
2. Still on the Map Fields tab, click in the Target Field Expression cell for the Field1 target field to open the RIFL Expression Builder.
3. Write the following expression in the Expression Builder window for the target field.

Left(a, Len(Trim(a))-1)




This expression performs the following: Counts the number of characters that are stored in the variable ""a"" after any leading and/or trailing spaces are stripped, subtracts one from that count, and returns the leftmost number of characters based on that calculation.

4. Click OK to close the RIFL Expression Builder window.

Source Event Handlers

The Source Event Handlers are one area where you write expressions using RIFL to manage sophisticated data transformation instructions in the Map Designer. The following Event Actions will contribute to the file format transformation demonstrated in this sample.

To access the Event Handlers locate and click on the ""Map All"" button in the button bar. You will now see four quadrants in the Map Designer window. You may wish to maximize the Map Designer window and re-size the dimensions of the quadrants to make it easier to see and access the selections you will be using. The Source event handlers are in the upper left quadrant.

Follow these steps to set up the source event handlers and actions.

1. In the Source tree (upper left quadrant) locate and click on the Event Handler node labeled ""AfterEveryRecord"".
2. In the quadrant to the right, click in the topmost cell under the column heading ""Action"".
3. Click on the down arrow, scroll down in the list of event actions, and select ""Execute"". A new window will open.
4. In the lower portion of the new window, click in the topmost cell of the ""Value"" column. An ellipsis will appear.
5. Click on the ellipsis to open the RIFL Script Editor.
6. Enter the following expression on multiple lines in the script editor:

If b == 0 Then

a = Records(""R1"").Fields(""SKU"") & "" "" & Records(""R1"").Fields(""SIZE"") & ""|""

b = 1

Else

a = a & Records(""R1"").Fields(""SIZE"") & ""|""

End If

7. Click the OK button to save the RIFL expression and to close the Script Builder for this event action.
8. In the Source tree (upper left quadrant) locate and click on the plus sign to expand the Event Handler node labeled ""Data Change Events"".
9. In the ""Data Change Events"" tree, locate and click on the Event Handler node labeled ""OnDataChange1"".
10. In the quadrant to the right, click in the topmost cell under the column heading ""Action"".
11. Click on the down arrow, scroll down in the list of event actions, and select ""ClearMapPutRecord"". A new window will open.
12. To accept the default settings for this event action, simply click on the OK button at the bottom of the event handler window to save and close it.
13. At the top of the upper right quadrant, locate the DataChangeMonitor picklist.
14. Set the DataChangeMonitor to look at the source ""SKU"" data field by selecting ""Fields(""SKU"")"" from the picklist.
15. To the right of that picklist, locate another box and select ""Suppress first ODC event/Fire extra ODC event at EOF"" from the picklist.

This completes the source event handlers and events actions setup.

Target Event Handlers

The Target Event Handlers are another area where you write expressions using RIFL to manage sophisticated data transformation instructions in the Map Designer. The following Event Action will contribute to the file format transformation demonstrated in this sample.

To access the Event Handlers locate and click on the ""Map All"" button in the button bar. You will now see four quadrants in the Map Designer window. You may wish to maximize the Map Designer window and re-size the dimensions of the quadrants to make it easier to see and access the selections you will be using. The Target event handlers are in the lower left quadrant.

Follow these steps to set up the target event handler and action.

1. In the Target tree (lower left quadrant) locate and click on the Event Handler node labeled ""AfterPutRecord"".
2. In the quadrant to the right, click in the topmost cell under the column heading ""Action"".
3. Click on the down arrow, scroll down in the list of event actions, and select ""Execute"". A new window will open.
4. In the lower portion of the new window, click in the topmost cell of the ""Value"" column. An ellipsis will appear.
5. Click on the ellipsis to open the RIFL Script Editor.
6. Enter the following expression on multiple lines in the script editor:

a = """"

b = 0

7. Click the OK button to save the RIFL expression and to close the Script Builder for this event action.

This completes the target event handlers and events actions setup.

Save Your Map

As with any work you do, we recommend that you save your work periodically. So click on the Save Map button in the button bar and save the transformation map. You can accept the default filename, or name it something like: My_Vertical_to_Horizontal_Transformation.map.xml

Just be sure to retain the .map.xml file extension.

Validate Your Map

To verify that the expressions you entered in the map are syntactically correct, click on the Validate Map button in the button bar. If the resulting message indicates that the map is valid, proceed to the Run Your Map section. But if the resulting message indicates that the map is not valid, go back to steps 3 and 4 above where you entered the expressions and correct any mistakes, typographical errors, etc. that would cause the error. If you made any changes, save your map again.

Run Your Map

Finally, you get to run your map, create the target file, and see the results of your work. Click on the Run Map button in the button bar. When the map has finished running, proceed to View Your Target Data.

View Your Target Data

If all worked as intended, you just created a target data file into which 3 records were inserted, each of which contains the source SKU in a single row, a space, and with each SIZE value for that SKU separated by a pipe (vertical bar). To view the contents of the target data file without having to exit the Data Loader, click on the Target Data Browser button in the button bar. You can verify that the data is accurate and complete by also opening the Source Data Browser and placing the source and target browser windows side by side on your workstation's desktop.