Combine select set of Excel tabs from one file into one input tool

[kheuer]

Hello,

I have an Excel file with 20 worksheet tabs. 17 of them have the same data schema, 3 of them are completely different. I would like to combine the 17 worksheet tabs into one data input. I played around with the Dynamic input tool but could not configure it to ignore the 3 worksheet tabs that are different. What is the best way of accomplishing this task?

Thanks much,

Kristina

[dataMack]

If you install the Crew Macro pack (http://www.chaosreignswithin.com/p/macros.html ) there is a wonderful tool called the 'Wildcard XLSX Input' generously contributed by the legendary @Joe_Mako which will read in all the tabs and excel files regardless of data structure.

[kheuer]

Oh boy, I just downloaded it and I don't know if I can wrap my head around this yet. It looks very advanced and I am a total newbie.

Any other suggestions to select some but not all tabs?

[Claje]

Do you know the names of the sheets you want to exclude?

[kheuer]

@Claje - yes, they are the same three static names (Summary, Detail, Sales).

[Claje · Accepted solution]

So one of the Table Options on the Input Data tool is "List of Sheet Names".

You should be able to do a flow something like this screenshot:

Let me know if this makes sense, or if you're still missing information you need to accomplish this.

[kheuer]

@Claje - thanks for the solution. I believe that this would work, but Alteryx claims that the data schema is different. What is the best way of troubleshooting what is different? All of the worksheets tabs are the same since in the process I am running before Alteryx bursts the data into individual sheets. I am puzzled as to why it thinks that there is a difference. Thoughts?

[Claje]

Hi,

Probably the best way to do this test/validation would be to set up a batch macro.

This sounds complex but should be fairly straightforward.  I have a batch macro that should meet your need uploaded inside the attached YXZP file in this thread:

https://community.alteryx.com/t5/Boston-MA/Workflow-Presented-during-9-28-User-Group-Meeting/gpm-p/8...

Let me know if you can try using that macro based on the example presented.  If not I'll try and provide a detailed explanation

[kheuer]

Hi @Claje,

Is there an option to ignore the data schema and let the Dynamic Input tool combine all records without the data validation checks?

[Claje]

Hi,

Unfortunately there is not a setting like that in Dynamic Input to my knowledge.

I believe you already have everything configured so you have your filenames and sheetnames?

If so, try the attached macro instead of the dynamic input tool.  It expects a format of Filename|Sheetname in one field.

Let me know if this helps!