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

Alteryx designer Discussions

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

Batch join macro - XLSX files with different sheet names

Meteor

Hi all,

 

I need to combine dozens of XLSX files; while they share the same column structure, the sheet names are different (e.g. REGION_project) and there are multiple sheets per xlsx file.

 

I've tried using a simple batch macro + directory, but it doesn't seem to work when dealing with multiple sheets of differing names, and that's where my expertise ends.

 

Any advice on best way to tackle this? Attached sample workflows highly appreciated for this Alteryx newbie.

Attached a sample of what the XLSX data I'm dealing with looks like (including some that are totally blank)

 

Many thanks in advance!

Alteryx Certified Partner
Alteryx Certified Partner

Hi @drewh 

 

The same way you can set up a batch macro to read your data, you could set up a batch macro to read the sheet names of your Excel File.

 

Using Directory Tool + this batch macro would give you a full list of Sheet Names. 

Up to you how you would handle them (use filter tool to get only the sheet names you need to union, or whatever)

 

 

batchsheetofnames.PNG

 

The next step would be add another batch macro to read your data, as you might be familiar with it already.

 

Cheers,

Meteor

Hi @Thableaus thanks for the quick response - 

 

I used your tip to pull the sheet names; I have the list of sheets I want to use via a Filter function, but here's where I get stuck. 

 

Usually I use a batch macro via a directory, but unsure how to use that in this case following this initial macro? Can you provide a little more color to this next step?

 

Untitled.png

Alteryx Certified Partner
Alteryx Certified Partner

Hey @drewh 

 

Sure!

 

The next step would be joining the Directory Tool File Name with your File Name from Filter Tool.

The result of the Join Tool would give you the Full Path of your files and respective sheets.

 

Then, the last thing to do is to add a formula Tool. [Full Path] + "|||" + [Sheet Name]. This field created will be used as control parameter to modify your batch macro that actually reads the data and union it.

 

Batchmacro.PNG

 

 

Hope you get the concept of it!

 

 

Cheers,

Meteor

Thank you @Thableaus this was very helpful!

Labels