Inputting Excel tabs with different (and multiple) header rows

Question

I'm really stumped on this one.

I have an excel file with a separate sheet for each month.

Each sheet has a 3-row header which includes some standard data,  but some that is unique to that month.

The first several columns of data are mostly standard...Site, City, Start and End dates, etc.

Then the sheets start to vary.

Row 1 of header --Month name (Fiscal month, not calendar month)  merged across the entire month.

Row 2 of header -- Day of Week

Row 3 of header --Day of month

I think I would like to have a field name for the data that includes the Month-Day.   I'm not sure the day of week matters

I'm getting really stuck because each month does not necessarily start on the first of the month.

Please see my attached file for the structure and what I think I would like the proposed structure to be.

Many thanks!

Sample.xlsx

KGT · Accepted Answer

There are a couple of keys to this.

* In the input tool, select "First row contains data". This will give you consistent headers (F1,F2....) and means the headers are already "Data" not "Metadata"
* Don't be afraid to split the headers off and deal with them separately.

Once you have one process, extending that to multiple files can be done with a batch macro pretty easily. I've attached that as well. For 3 files, I wouldn't bother with the macro if you're not comfortable with them yet. just copy and paste the flow each time.

* After splitting the headers it's a mix of Prep/Transform/Join to get all rows into the one. The Transpose is the easiest way to fill across (by filling down)
* Replace the headers on your data using the dynamic rename.
* Also: You will have to sort out where the date goes to the next month if it still has February in the first line to input the next month.