Normalize from an Excel sheet with dynamic number of columns and column headers

[maxbobse]

I have an Excel sheet with a structure like this:

I want to convert this to a structure where I have 1 row per subject/date combination with the columns date, subject, quantity
I think that the Row Normaliser can do that in general but how do I achieve it with a dynamic number of columns?

Max

[liosc]

Hi. Have you considered using Metadata Injection (https://hop.apache.org/manual/next/pipeline/metadata-injection.html)?

[maxbobse]

Yes but how would I get the information about the names of the column headers in the Excel sheet?

[hansva]

The only clean way to solve this is by creating an Excel input that has X columns defined, where X is the maximum number of columns you are expecting. As this seems to be date-based you could create an input with 365 columns then Normalise and then remove all rows that don't have data. Or you could add an extra sheet containing a pivot of the data to make your life simpler I don't know if you have any control on how the file is being delivered.

[maxbobse]

If I do that, I loose date the information of the column headers:

And no, unfortunately no control over how the data is delivered :/

[hansva]

Disable "header" in the content section, and you will get your header as the first row.
you will have to read everything as strings and then convert them to the correct types further down the stream

[hansva]

Or you can do that as a second input and only read 1 row with the header turned off, then do the same normalization and join the data back on the key. this way you will have an extra column containing the date

[dave-csc]

Take a look at these discussions: #4522 and #5128

I attached a custom-made transform, named "Anti-normalizer", that should do the job: you need to specify the "grouping fields" (that must be known, "Subject" in your case), a "key field name" (i.e. "date") and a "value field name" (i.e. "quantity"). The result should be a table with the fields subject, date and quantity (should, because the columns headers are expected to be strings rather than dates...)

For extra sake: do you have the possibility to transpose the Excel file, in order to have "Date", "B2C" and "B2B" as headers? For counting and processing matters, it would be waaaaaay simpler...

Hope this helps :)

[maxbobse]

Thanks but I am not sure how that helps me to dynamically set the column headers for the Excel input?