Key-Value Pair data parsing

Hi,

I am having a file with key-value pair data structure like below:

Key Value
fld_1 val_11
fld_2 val_12
fld_3 val_13
   
fld_1 val_21
fld_2 val_22
fld_4 val_24
   
fld_1 val_31
fld_2 val_32
fld_3 val_33
fld_4 val_34

 

I would like to conver the data structure into following format:

fld_1 fld_2 fld_3 fld_4
val_11 val_12 val_13 <null>
val_21 val_22 <null> val_24
val_31 val_32 val_33 val_34

 

I have used One to Many node then carry out value replcement with Java Snippet node through coding and finally use GroupBy node for merging. This gives me the correct result but taking too much time to parse in case of large files.

Can you suggest some efficient way to carryout the transformation?

It would be the pivoting node.

But you don't have groups in your final table. How are you deciding which of your values is in row 1, row 2 and row3? 

I am injecting a uniquely an interger id again fld_<n>. and I am grouping againt that id to merge the rows.

 

Can you suggest some other way to carry out above transformation?