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

Jul 28

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

Question:

Is there a way to change the orientation of my data from rows to columns?

Answer:

This ""DataTools HowTo"" contains an example of a data transformation showing how data stored in a row format in your source data file can be transposed into a columnar format in your target data file.

The DataTools product used in this sample transformation map is the Data Loader to Fixed Text.

Source Connection

The source connector is ASCII Delimited, and the source file is TUTOR1.ASC. This is a sample CSV text file that resides in the main DataTools installation folder on your workstation.

Target Connection

The target connector is ASCII Fixed, and the target file that will be created after running the transformation map will be TRG_TRANSPOSE.ASC. You may write the target file to any folder to which you have access and permissions.

Transformation and Map Properties

To accomplish the horizontal-to-vertical transformation, the Event Handlers and Event Actions must be used in the Transformation and Map Properties window. To access this window click on the View menu and select Transformation and Map Properties from the menu options.

Four Event Actions are created in the Transformation Map Properties. The following describes how to create these Events.

Global Variables

1. In the tree on the left side of the window, click on ""Global Variables"".
2. In the grid on the right side of the window, click in the top cell in the Name column and click on the ellipsis.
3. In the new window that opens, enter ""Count"" (without quotation marks) in the Variable Name field, and select ""Variant"" from the Variable Type picklist. Optionally, enter ""Initialize a conversion global field counter."" in the Description field. Then click OK to save this variable.
4. Back in the Transformation and Map Properties window, click in the second row cell in the Name column and click on the ellipsis.
5. In the new window that opens, enter ""Write_Cnt"" (without quotation marks) in the Variable Name field, and select ""Variant"" from the Variable Type picklist. Optionally, enter ""Calculate and initialize the number of times a target record is written for each source record."" in the Description field.
6. Click OK to save this variable.

BeforeTransformation

1. In the tree on the left side of the same window, click on ""BeforeTransformation"".
2. In the right side of the window, click on the ellipsis in the Action column.
3. Select ""Execute"" as the Event Action.
4. In the lower portion of this window, enter ""Write_Cnt = Sources(0).FieldCount"" (without quotation marks) in the Value column.
5. Click OK.

AfterTransformation

1. In the tree on the left side of the window, click on ""AfterTransformation"".
2. In the right side of the window, click on the ellipsis in the ""Action"" column.
3. Select ""Execute"" as the Event Action.
4. In the Comments box enter ""Set global variables to zero"".
5. In the lower portion of this window, click in the Value column and click on the ellipsis to open the RIFL Expression Builder.
6. In the Expression Builder, enter the following on three lines, and be sure to type a single quote (also called an apostrophe) at the beginning of the first line:

'These expressions clear the conversion globals from memory after the conversion is complete.

Count = Null

Write_Cnt = Null

7. Click OK.

Back in the Transformation and Map Properties window, click OK to save the variables you created.

Event Handlers & Actions

Two Event Actions are created in the source R1 Event Handlers on the ""Map All"" tab. To access these advanced features click on the Map All button in the button bar after making the source and target connections and going to the Map Fields tab.

AfterEveryRecord - Execute

1. In the upper left quadrant of the Map All tab do the following steps.
2. In the tree on the left side of the window, click on ""AfterEveryRecord"".
3. In the right side of the window, click on the ellipsis in the ""Action"" column.
4. Select ""Execute"" as the Event Action.
5. In the lower portion of this window, click in the Value column and click on the ellipsis to open the RIFL Expression Builder.
6. In the Expression Builder, enter the following on two lines:

' Reset counter.

Count = 1

7. Click OK.

AfterEveryRecord - ClearMapPutRecord

1. Still in the ""AfterEveryRecord"" event handler, and in the second row in the right side of the window, click on the ellipsis in the ""Action"" column.
2. Select ""ClearMapPutRecord"" as the Event Action.
3. In the lower portion of this window, verify that the Value column for ""target name"" is set to ""Target"".
4. Verify that the Value column for ""record layout"" is set to ""R1"".
5. Click in the Value column for the ""count"" parameter and click on the ellipsis to open the RIFL Expression Builder.
6. In the Expression Builder, enter ""Write_Cnt"" (without quotation marks), and click on OK to close the Expression Builder.
7. Click OK again to close the AfterEveryRecord event handler dialog box.

Data Field Mapping

In this sample, there are two source fields mapped to the target file, as follows:

• One field is mapped as header information
• One field is mapped as data

The mapping expressions for each target field are written as follows. The expressions in this transformation capture source metadata using the metadata functions; for example: Fields(Count).Name

To access the data field mapping window, click on the Map Fields button in the button bar. Follow these steps to complete the data field mapping.

Map the Field Names

1. In the right side of the window, click in the topmost blank cell under the Target Field Name column and enter ""FieldNames"".
2. Still in the right side of the window, click in the cell below where you entered ""FieldNames"" and enter ""FieldValues"".
3. Header records are not a standard option when creating fixed ASCII text files, so these field names are simply placeholders. Neither ""FieldNames"" nor ""FieldValues"" will be written to your target file.
4. In the Target Field Expression cell for FieldNames, click on the ellipsis to open the RIFL Expression Builder, and enter the following special metadata expression on two lines:

' Writes the field name based on the field count.

Sources(0).Fields(Count).Name

5. Click OK to close the Expression Builder.

Map the Field Values

1. In the Target Field Expression cell for FieldValues, click on the ellipsis to open the RIFL Expression Builder, and enter the following special metadata expression on three lines:

' Writes the value of a field and increments the count to write the next value in the next record.

Sources(0).Fields(Count)

Count = Count +1

2. Click OK to close the Expression Builder.

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: Transpose_Data_From_Horizontal_to_Vertical_Orientation.map.xml

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

Validate Your Map

To verify that the expressions you entered in the various places 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 each step above where you entered an expression and correct any mistakes, typographical errors, etc. that would cause the error.

Run Your Map

Finally, you get to run your map, create the target file with the transformed data, 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 with the transposed data orientation. 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. The first column should contain 99 repetitive ""groups"" of the column header row values from the source data file. The second column should contain 99 repetitive ""groups"" of the data records from the source data file. You can verify that the data is accurate and complete by opening the Source Data Browser and placing the source and target browser windows side by side on your workstation's desktop.