Converting columns to rows - 7.3

Delimited
Version
7.3
Language
English
Product
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Real-Time Big Data Platform
Module
Talend Studio
Content
Data Governance > Third-party systems > File components (Integration) > Delimited components
Data Quality and Preparation > Third-party systems > File components (Integration) > Delimited components
Design and Development > Third-party systems > File components (Integration) > Delimited components
Last publication date
2024-02-21

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

  1. Create a Job named UnPivotColumnsToRows, composed of a tFileInputdelimited, a tUnpivotRow and a tLogRow component linked by three main rows.
  2. 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.
  3. 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.
  4. Double-click tUnpivotRow to add an input column named Id in the Row keys box.
  5. 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.
  6. 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.
  7. 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.