community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Multiple excel files multiple tabs different schema

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.

Regards,

Olu
Highlighted
Asteroid

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.

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.

 

Best,
Yalmar

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.

Meteoroid
Asteroid

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

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.

Atom

Hi, 

 

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.

 

image.png

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

Asteroid

Thanks.

 

I will give it a try.

Labels