Transaforming excel data grouped in sections

[ankur]

Hi ,

I am using kettle 4.0.1 .I have an excel file with this format

Category 1
Line1 ABC
Line2 XYZ

Category 2
Line3 ASD

which I want to convert to :

Line1 ABC Category1
Line2 XYZ Category1
Line3 ASD Category2

Can anyone help me how can I achieve this using kettle.

[Nexus]

I'm trying to understand the format of your excel file.

The category titles (Category 1, Category 2) are in the same column as the values (ABC, XYZ, etc.)? So 'ASD' actually is on Line4 in stead of Line3?

[ankur]

Attaching sample file for reference.
Sample.xls

[Tony.Cloud2Land]

You can use Modified Java Script Value to help you with this.

Here is a ktr transformation that will do what you want!

Categories.ktr

[ankur]

Thanks Tony .

That worked perfectly.