topic Re: Transpose Columns to Rows in Talend Studio

Transpose Columns to Rows

RAJ6 — Thu, 19 Oct 2017 08:23:39 GMT

Hi Talend Folks,

In my excel have 400 rows and 500 columns are there.
I have a input excel source like below screenshot format

I need to convert data like below screenshot format using talend tool.
Expected Output.

Please tell me how to Transpose Columns to Rows data above screenshots.

Re: Transpose Columns to Rows

Anonymous — Thu, 19 Oct 2017 10:58:33 GMT

Hi there,

Talend and similar ETL tools are very much about dealing with fixed, structured data, and the standard components are geared up for this.

In this case, there are presumably a dynamic number of columns, and the column headings are going to change, which means these standard components and standard approaches aren't suitable.

As I've explained in your other thread regarding the column names in Talend, you also won't be able to use the dates in your spreadsheet as columns within your job.

Assuming you can't just pivot this in Excel itself, which is very quick and easy to do, then you will either require custom code in a tJavaFlex component, or maybe a custom component.

Take a look on Talend Exchange (https://exchange.talend.com/) and see if there's something suitable.

Regards,

Chris

Re: Transpose Columns to Rows

Anonymous — Wed, 24 Jan 2018 06:38:56 GMT

Hi ,

I had a similar scenario where i want to transpose some columns to rows ,where i used tjavaflex to achieve .

Tjavaflex Details :

Start code - Here get the column names which we need to populate in rows and make a for loop with that number, In my case i have 5 columns and i want to transpose last 3 columns ,

String splitString = row7.toString().substring(row7.toString().indexOf("[")+1,row7.toString().length()-1);

String[] arr1;
arr1 = splitString.split(",");
String[] arr2;
int[] numArr = {2,3,4}; // put the position number (counting from 0) of the columns you want to transpose - for me last 3 cols
String temp = "";
for(int j = 0;j<numArr.length;j++)
{
if(j!=numArr.length-1)
temp+=arr1[numArr[j]]+",";
else
temp+=arr1[numArr[j]];
}

arr2 = temp.replaceAll("=null","").split(",");

for(int i =0 ;i<arr2.length;i++)
{

Main Code - Here Populate the two new columns ,

row8.col_name = arr2[i];
String temp2 = row7.toString().substring(row7.toString().indexOf(arr2[i]+"="),row7.toString().length()-1)+",";
row8.col_value = temp2.substring(temp2.indexOf("=")+1,temp2.indexOf(","));

End Code - Close the forloop as ' } ' .

}Test JobSchema - fixedFlowInp & tjavaflexSample InputAchieved Output (Transposed one)

Re: Transpose Columns to Rows

Anonymous — Thu, 11 Apr 2019 13:15:50 GMT

The simple solution for this is use tUnpivotRow(from talend exchage)

follow the link for its explanation

https://www.datalytyx.com/how-to-denormalise-a-dataset-using-talends-tunpivotrow/

Re: Transpose Columns to Rows

fjblau — Mon, 19 Dec 2022 10:30:56 GMT

tUnPivotRow is NOT compatible with Talend Studio 8

Re: Transpose Columns to Rows

jlolling — Mon, 19 Dec 2022 18:04:40 GMT

You can use the component tFileExcelSheetInput together with tFileExcelSheetInputUnpivot.

https://github.com/jlolling/talendcomp_tFileExcel/blob/master/doc/tFileExcelSheetInput.pdf

These components are part of the well known excel components tFileExcel* component suite.

It allows also to have a variable number of columns and you do not have to deal with the massive amount of columns at all. Everything is done automatically.