Read data multiple tabs with different headings

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.

Developer Tools

Batch Macro

Accepted answers

DataNath

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.

All comments

DataNath

Hey @tandon, will the data column always have 'date' somewhere in the header? If so you could use a straightforward dynamic rename formula like so:

Before:

After:

Can also use the following which looks for the '£' symbol to indicate sales:

Just shout if not. Thanks!

Dynamic date and sales rename.yxmd

Dynamic date and sales rename - Pound symbol.yxmd

IraWatt

Hey @tandon,

Would be good to have your workflow but what I would do is this:

As each file comes in your macro transpose it then use find and replace to sort the columns to a standardised output so they can all union by name.

Any questions or issues please ask
HTH!
Ira

Example 305.yxmd

tandon

Thanks @DataNath. there are more then 50 tabs in my excel and all have different abbreviations for date and Amount. I tried to use above approach but then I need to code for all different abbreviations for date and amount. I am trying to design something dynamic like and avoid coding formulas for 50 different tabs.

tandon

Thanks @IraWatt . forgot to mention - I have excel file with more then 50 tabs. I am unable to upload workflow due to restriction.

DataNath

Thanks for clarifying @tandon. Edited my original post but I think the transpose option I added since will help! This will bring all the headers into columns and then conduct the checks from there.

binu_acs

@tandon one way of doing this with the Union tool with the property AutoConfig by position

Union_30_05.yxmd

tandon

Thanks @binu_acs . Union may not work as I have data in single excel with multiple tabs

tandon

Thanks @DataNath . I am expecting output as below . I will explore transpose approach however it seems to be expensive (in terms of coding).

IraWatt

@tandon The find and replace method just requires a lookup table if you dont want to code.

DataNath

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.

binu_acs

@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.

CombineTabs.yxzp

tandon

Thanks @binu_acs . 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).

binu_acs

@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.

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703