Iterate through rows and pivot / transpose rows and columns.
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.
@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.
@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.
