Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!

Multiple excel files multiple tabs different schema

8 - Asteroid
Hi All,

Hope you’re well?

So I have 3 tabs per individual excel file. There are about 30 excel files.
The tabs have different schema to one another.

The first two tabs share a few column names in common whilst the third tab has only one column name in common with the first two tabs.

I’m trying to create a workflow and your help will be very much appreciated.


9 - Comet

I've had to do something similar too.  Depending on the version of Excel your files have been saved in, you may be able to get a list of the tab names, otherwise I had to write some code in R to list the tab names.  Of course you won't have to do this if the tab names are static every time.  You will need the 3 pipes after the filename to dynamically open the file, e.g. <filepath>\<filename>.xls|||<tabname>.


Because your schema's are different for each tab, I would recommend creating an iterative macro and dynamically opening the file and tab on each iteration otherwise you may get schema different error messages.

8 - Asteroid

Thanks for your response.


The tab names are static every time. It's with xlsx extension.


Do you or does anyone know of the most suitable workflow to use in this instance?


Thanks in advance.

Alteryx Partner

Hi @Ojay,


Are all first tabs in the same structure? Of do they differ between files?

Otherwise you could create a macro that imports all first tabs, second tabs and third tabs.



5 - Atom

Hi Ojay,

have you tried already to use 3 different Input Tool, one for each tab, configure as @spainn said but instead of the file name try to use an * e.g. <filepath>\*.xls|||<tabname> ( all the input files have to be in the same folder).

In this way, you should be able to load, for example, Tab 1 of all 30 files, then you can do the same for the other 2 tabs.

Then with a union tool, you can merge everything together.

8 - Asteroid
8 - Asteroid

All the first tabs are in the same structure as are the second tabs and the third tabs between the files.

8 - Asteroid

I'm currently trying this and will let you know how it goes.


The ultimate goal is to create one dataset with the required columns from each tab.

5 - Atom



So aside from getting a custom macro, or possibly one of the crew macros as others have suggested, you're probably best of just going and pulling each tab independently and then combining.


You can use the directory tool followed by dynamic input to do this in bulk.


The workflow probably looks something like this.



I don't know if you the join is rich for you, but it's easy to change. 

8 - Asteroid



I will give it a try.