topic Re: Convert Columns to Rows in Talend Studio

Convert Columns to Rows

Anonymous — Mon, 01 Apr 2019 20:24:05 GMT

Hi Community,

I am trying to convert columns to Rows and rows to columns from an input excel file to an output table.below is my scenario,

Source excel

output expected is given below

output expected

Please help me to design a Talend job to get this output from the given source file

Re: Convert Columns to Rows

DataTeam1 — Tue, 02 Apr 2019 00:46:10 GMT

I used tPivotRow component from Talend Exchange

https://exchange.talend.com/#marketplaceproductoverview:marketplace=marketplace%252F1&p=marketplace%252F1%252Fproducts%252F737&pi=marketplace%252F1%252Fproducts%252F737%252Fitems%252F1043

My example of data

Job

Effect

Don't forget to give kudos when a reply is helpful and when your query is answered, please mark the topic as resolved.

Re: Convert Columns to Rows

Anonymous — Tue, 02 Apr 2019 14:15:06 GMT

@DataTeam
Thanks for the quick response. I downloaded the tUnpivotRow component but unable to find it in talend open studio. i uploaded the downloaded file in Preferences>Talend>component. but it is not showing up in studio palette.

I am not using the subscription version. Is that the issue with tUnpivotRow.?

Re: Convert Columns to Rows

akumar2301 — Tue, 02 Apr 2019 16:02:20 GMT

Hello if you are not willing to use custom component , you can do stuff your own , more dynamically.

step 1 : Get all the ridenames (1st Column)
Step2:Get number of Column in Excel using tJava
Step3 : Loop through each Month Columns and Transpose data. ( 1st and Last Two Rows only)
Step4 : Do cross join(tMap) between result of Step1 and Step3.

Above will work independent of number of columns.
Attaching a Job and sample input I use . Change Global Variable (tSetGlobalVar) accordingly.
 
excelinput_0.1.zip
in.xlsx

Re: Convert Columns to Rows

DataTeam1 — Tue, 02 Apr 2019 17:11:35 GMT

@SP_BI you have to unzip the zip file of this component.

I also use open studio so you can use this component in your case too.

My solution does what you need but try to check @uganesh solution because his proposal seems to be much more flexible