Batch join macro - XLSX files with different sheet names

[drewh]

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!

[Thableaus]

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)

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

Cheers,

[drewh]

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?

[Thableaus · Accepted solution]

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.

Hope you get the concept of it!

Cheers,

[drewh]

Thank you @Thableaus this was very helpful!