Iterate through rows and pivot / transpose rows and columns.

ecurren-lmi — Mon, 18 May 2020 15:59:18 GMT

Hello folks,
I’m very new to Talend and I’m having a great deal of difficulty accomplishing what seems to be to be a very simple task.

The fundamental objective is to take data from a set of Excel spreadsheets and move the data into a database. The job(s) will be triggered when a new XLSX file is dropped into an identified directory.

The Excel file has 15 different worksheets, and each worksheet has a different schema. There are a total of 700 to 800 columns total amongst the 15 worksheets.

The tFileInputExcel component appears to be limited to one schema across all of the worksheets so I am using the tFileExcel-Components from Jan Lolling which allows for different schemas to be used.
When a new file is dropped into the directory, the process iterates through the worksheets. Each worksheet is sent to a tMap component so that the specific worksheet is assigned its associated schema from the schema repository. This is where I’m running into the issue that I need help with.

For each row in the worksheet, I need to transpose the rows and columns in such a way that the column name and associated row value are output.

For example, if I have the following data as input:
Column1 Column2 Column3 Column4
RowValue1 RowValue2 RowValue3 RowValue4
RowValue5 RowValue6 RowValue7 RowValue8

The output would be:
Column1 RowValue1
Column2 RowValue2
Column3 RowValue3
Column4 RowValue4
Column1 RowValue5
Column2 RowValue6
Column3 RowValue7
Column4 RowValue8

I’ve looked at a number of different posts about issues that appear to be similar to this, but I’m not finding a solution that will work for me.
All help is greatly appreciated.

Re: Iterate through rows and pivot / transpose rows and columns.

manodwhb — Tue, 19 May 2020 04:44:14 GMT

@ecurren-lmi , you can achieve the below example with tUnpivot component, which is an external component. You need to download from the exchange https://exchange.talend.com/.

check the below link on how to install that component.

https://community.talend.com/t5/Design-and-Development/talend-exchange-install-tQuartzScheduler/m-p/220034#M121185

topic Iterate through rows and pivot / transpose rows and columns. in Talend Studio

Iterate through rows and pivot / transpose rows and columns.

Re: Iterate through rows and pivot / transpose rows and columns.