Read data multiple tabs with different headings

[tandon]

I have excel file with multiple tabs with different headings and different order. I am able to design the batch macro / dynamic input which corrected the order however for different headings, need to handle it. please comment.

Below are example where I have data in different tabs. I would like to import all in two columns i.e. Date and Sales. 

[DataNath · Accepted solution]

The transpose route shouldn't be coding-intensive (or require any extra for that matter). All it does is bring all headers into columns, checks if they contain '£' and if so, assigns that as 'Sales' and any others as 'Dates'. Then the dynamic rename tool finds the original header in the initial input stream and replaces it with the new 'Date'/'Sales' header. Therefore, this option should just be ready to plug into your flow. Can you give it a try? If not, it may help if you can provide us with an example of your flow or inputs so we can put something together. Thanks!

Edit: To clarify, this would be after the batch macro had brought all of the files together and they were all in on table with varying headers.

[binuacs]

@tandon If you are reading from multiple tabs from a same file you need to first read all the tabs name then read the contents from these tabs using a batch macro. Attaching a workflow which first reads all the tabs from the input excel file then combined based on the record position. 

[tandon]

Thanks @binuacs . I tried using your file but not sure whether it gives desired output. I see, it generate 36 records however it's repeating records (6 times from tab 1 only). 

[binuacs]

@tandon Can you provide some dummy records and expected output file ?

[tandon]

apologies for delay in response. 

[tandon]

@DataNath - I figured out by using your suggestion i.e. used formula to change the headings. I did below

Loaded all data (using batch macro) in single table and then designed look up table to find out which columns needs to be updated.

unable to load file due to restriction. 

@binuacs , @IraWatt - thanks again for your help.