Map values from single column to multiple columns where column name is in the same cell as the value (see screenshots)

[Anonymous]

I have a spreadsheet that is only one column. Each row has the name of a field and a value. I need to get each set of rows, which has a variable number per set, into a single row. Any ideas how I can accomplish this?

It should come out like this:

Thanks!

[vapukov]

Hi,

with this type of question - you never will have final advice. Too many IF, and too many behind the scene 🙂

but some ideas, please find below.

Idea based on provided by you data, which:

• have implicit groups of rows
• have standard separator between groups

in this case you could use next components:

 

tFixedFlowInput - you could replace to your own input component, with a single column in the schema (in my example it named "data")

tJavaFlex - assign group number based on default separator "--------"

tFilterRow - reject empty and separator rows from the flow

tExtractDelimitedFields - split column data into 2 columns by separator

and finally,  tPivotToColumnDelimited transform output result

output result:

you could addition clean/remove column grp from output file

one more time - it is just an idea, final job will always depend from you real data and other restrictions

[akumar2301]

There is a workaround , but it is by converting your file to  LDIF file .

you need read your excel and convert it like this ( dn : linenumber with previous line blank is mandatory)

dn:1
Field1:1
Field2:2

dn:2
Field1:3
Field2:4

dn:3
Field1:4

the use tFileInputLdif and tlogrow row. Output will be like this 

https://help.talend.com/reader/KxVIhxtXBBFymmkkWJ~O4Q/eRexaC1aghU1uSFx2gLqMQ