Code for converting data into a particular format

[kaushik_birmiwal]

Hi All,

I have been facing this recurring issue. We need to analyze the spend as per product and SKU by week, months, quarters etc. The dump from the tool is in a raw form and we end up spending lot of time in converting the data into a format which can be further used to apply pivots. I am relatively new to coding and hence would like to seek help.

I have the data in below format:

[TABLE="width: 517"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Year[/TD]
[TD]January[/TD]
[TD]January[/TD]
[TD]January[/TD]
[TD]January[/TD]
[TD]January[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]2016[/TD]
[TD]1[/TD]
[TD]8[/TD]
[TD]15[/TD]
[TD]22[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Week No.[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]2016[/TD]
[TD]SKU 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SKU 2[/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]2016[/TD]
[TD][/TD]
[TD]SKU 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]2016[/TD]
[TD][/TD]
[TD][/TD]
[TD]SKU 5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SKU 6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]Planned[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 3[/TD]
[TD]Rental[/TD]
[TD="align: right"]$6,000[/TD]
[TD="align: right"]$17,655[/TD]
[TD="align: right"]$5,000[/TD]
[TD="align: right"]$40,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 4[/TD]
[TD]listing fee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 5[/TD]
[TD]production cost[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]2016[/TD]
[TD][/TD]
[TD]SKU 7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]2016[/TD]
[TD][/TD]
[TD][/TD]
[TD]SKU 11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]2016[/TD]
[TD]SKU 8[/TD]
[TD][/TD]
[TD]SKU 9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SKU 10[/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]Planned[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]Rental[/TD]
[TD="align: right"]$6,000[/TD]
[TD="align: right"]$17,655[/TD]
[TD][/TD]
[TD="align: right"]$40,000[/TD]
[TD="align: right"]$5,000[/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]listing fee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]production cost[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I now need to convert this data into the below mentioned format, so that i can pivots for analyzing the data.

[TABLE="width: 568"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Product Category[/TD]
[TD]Product[/TD]
[TD]Week[/TD]
[TD]SKU #[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]AB[/TD]
[TD]Product 1[/TD]
[TD]1[/TD]
[TD]SKU 1[/TD]
[TD]$6,000[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]AB[/TD]
[TD]Product 1[/TD]
[TD]1[/TD]
[TD]SKU 7[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]AB[/TD]
[TD]Product 1[/TD]
[TD]3[/TD]
[TD]SKU 5[/TD]
[TD]$5,000[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]AB[/TD]
[TD]Product 1[/TD]
[TD]4[/TD]
[TD]SKU 6[/TD]
[TD]$40,000[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]AB[/TD]
[TD]Product 1[/TD]
[TD]5[/TD]
[TD]SKU 2[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]AB[/TD]
[TD]Product 1[/TD]
[TD]2[/TD]
[TD]SKU 13[/TD]
[TD]$17,655[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]AB[/TD]
[TD]Product 1[/TD]
[TD]2[/TD]
[TD]SKU 3[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]CD[/TD]
[TD]Product 2[/TD]
[TD]1[/TD]
[TD]SKU 8[/TD]
[TD]$6,000[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]CD[/TD]
[TD]Product 2[/TD]
[TD]2[/TD]
[TD]SKU 12[/TD]
[TD]$17,655[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]CD[/TD]
[TD]Product 2[/TD]
[TD]3[/TD]
[TD]SKU 11[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]CD[/TD]
[TD]Product 2[/TD]
[TD]3[/TD]
[TD]SKU 9[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]CD[/TD]
[TD]Product 2[/TD]
[TD]5[/TD]
[TD]SKU 10[/TD]
[TD]$5,000[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]CD[/TD]
[TD]Product 2[/TD]
[TD]4[/TD]
[TD]NA[/TD]
[TD]$40,000[/TD]
[/TR]
</tbody>[/TABLE]




Can someone please help me with this issue.

Thanks in advance.

Regards,
Kaushik