Convert Columns to Rows

[Anonymous]

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

[DataTeam1]

I used tPivotRow component from Talend Exchange

https://exchange.talend.com/#marketplaceproductoverview:marketplace=marketplace%252F1&p=marketplace%...

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.

[Anonymous]

---

@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.?

[akumar2301]

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

[DataTeam1]

@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