Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Read data multiple tabs with different headings

tandon
8 - Asteroid

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. 

 

tandon_0-1653925736677.png

 

15 REPLIES 15
DataNath
17 - Castor
17 - Castor

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:

DataNath_0-1653926139948.png

 

After:

DataNath_1-1653926158298.png

 

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

 

DataNath_2-1653926597115.png

 

 

Just shout if not. Thanks!

 

IraWatt
17 - Castor
17 - Castor

Hey @tandon,

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

IraWatt_0-1653926249965.png

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

tandon
8 - Asteroid

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
8 - Asteroid

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

DataNath
17 - Castor
17 - Castor

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.

binuacs
21 - Polaris

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

 

binuacs_0-1653926803794.png

 

tandon
8 - Asteroid

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

tandon
8 - Asteroid

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

tandon_0-1653927166963.png

 

IraWatt
17 - Castor
17 - Castor

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

Labels
Top Solution Authors