Converting columns to rows

Creating an example file to convert columns to rows

This procedure explains how to convert columns to rows.

Create a .txt file example with the following data:

Id;Name;Sex;Surgery
1;Shong;M;Y
2;Ross;M;Y
3;Elise;F;N

The columns will become rows as follows:

Id;Question;Answer
1;Name;Shong
1;Sex;M
1;Surgrey;Y
2;Name;Ross
2;Sex;M
2;Surgrey;Y
3;Name;Elise
3;Sex;F
3;Surgrey;N

To accomplish this task, install the custom component called tUnpivotRow available on the Talend Exchange. For more information about installing a custom component, see on Talend Help Center (https://help.talend.com).

Converting columns to rows

In this Job, you will use the tUnpivotRow and the tMap component to convert columns to rows.

This Job will use the following components:

a tFileInputDelimited component to read data from in.txt file,
a tUnpivotRow component to convert columns of the person table to rows,
a tMap to generate an output table,
and a tLogRow component to print output data on the console.

Procedure

Create a Job named UnPivotColumnsToRows, composed of a tFileInputdelimited, a tUnpivotRow and a tLogRow component linked by three main rows.
Double click tFileInputDelimited to open its Component view.
1. Fill the File name/Stream field with path of the .txt file you created for example, "D:/file/in.txt".
2. Change the Header field to 1.
Click the ellipsis button (...) to edit the schema of the component.
1. Delete the columns pivot_key and pivot_value.
2. Add a column named Id of Integer type and a three columns Name, Sex and Surgrey of String type.
Double click tUnpivotRow to add an input column named Id in the Row keys box.
Edit the tUnpivotRow schema and add an output column named Id of Integer type as below.

Note: Both output columns Pivot_key and Pivot_value are pre-defined columns and read only tUnpivotRow component.
Double click the tMap component to open its schema and drag the columns id, pivot_key and pivot_value from the Row2 table to the out1 table.
Respectively change the pivot_key and pivot_value name of the out1 table to Question and Answer.

Note: Because both columns (Pivot_key and Pivot_value) are read only in the tUnpivotRow component, you must change the column names in tMap to map to the target schema.

Executing the Job

Procedure

Execute the Job. The following results are output to the console: