How to load a crosstable with two header rows/2 attribute fields.

[richard_cioci]

I'm trying to load a table from Excel which we receive with 2 header rows. I can't change the source file when we get it each time, so I'm trying to figure a way to do a cross table with the 2 header rows which both have necessary information.

I've included a sample xls of the form of my source data and what I want it to load it in as.

To add to that, the top header is merged meaning that those columns are empty when loaded, so there also needs to be a transform to fill that header in.

Also, months change according to the last 10 months( or as in the sample, 5 months) of each product, so I can hardcode field names, for the dates, it has to be an * load.

I.e. next month, the last first month will be feb-15 and the last will be jun-15.

Any help is appreciated

[ingoniclas]

Great post with a fantastic solution, Stefan. I have used parts of your solution and almost () solved a similar problem as mentioned in this post. However, the remaining issue: I can't use your mapping solution to change my header since I need to load multiple tables in a loop, i.e. my header columns F, H, J each would have multiple rows with multiple dates, and the mapping would only work with the first date in the first row. Any ideas how to solve this issue?

I have also posted my issue in more detail here. It would be a blast if this could be solved.

[RobertTimm]

Just what I was looking for.  Thanks