Challenge #118: Think Like a CSE - A not-so-wild-wildcard input!

[EddieW]

The solution to last week's Challenge can be found here! 

We are thrilled to present another Challenge from our “Think like a CSE” series, brought to you by our fearless team of Customer Support Engineers. Each month, the Customer Support team will ask Community members to “think like a CSE” to try to resolve a case that was inspired by real-life issues encountered by Alteryx users like you! This month, we present the case of using a Wildcard in the Input tool.  

Below, we’ve provided the information that was initially available to the Customer Support Engineer who resolved the case. It’s up to you to use this information to put a solution together for yourself.

The Case: We have a co-worker who wants to import all (4) files from one of his folders into the workflow to do some analysis. He doesn’t like the idea of having four Input Data tools in his canvas so he decided to give the “Wildcard” input a try. However, when he runs the workflow, only 1 of the 4 files is imported. Our co-worker is not entirely sure why it’s not working.

Explanation of the Problem: Four input files (attached to this post) exist in the same directory.  Our co-worker attempts to bring in multiple .xlsx files into a workflow using a “wildcard” in the input tool: 

When he runs the workflow, only one of the four files is imported: Hm...not as "wild" as we thought...

Then, when he clicks the Input tool, an error appears: 

 What's going on?  Can you help our co-worker resolve the case of the Wildcard Input?  Download the four attached files, map your Input Data tool to your file location, and give it a try! 

Bonus Question: What do you recommend if our co-worker needs to import 60 different files and using wildcard is giving him all these random errors?  All these files come from a third party.

[NicoleJohnson]

My solution!

Spoiler

I love the smell of macros in the morning... :)

Because of the differences in schema and tab name, I chose to go the macro route and feed the directory of file names into a batch macro that would run each one individually and then output the results by column name... now I was a bit lazy, because I believe there would be issues if there was more than one tab per workbook, but for the case provided, this should work for any number of files!

Cheers,

NJ

[SuzanneMc]

Did some Alteryx community research ...  different names and file schemes need a one at a time batch macro, fun one! 

[patrick_digan]

Spoiler

Since we only have 1 sheet and it's an xlsx file, I would use a simple batch macro to accomplish the task.

[ggruccio]

Tried several routes!

Spoiler

Used a batch macro to get the sheet names dynamically then the same batch macro to get the data.  (It threw me that the sheet names and the schemas were different).

[trodriguez91]

The client is so close!  However, the input data tool will not read an excel with a tab name that is not consistent with Sheet1.  Therefore, when reading the spreadsheet for File4 we see that an error is raised indicating that the file may be corrupted.  To facilitate the reading of the different spread sheets with different schemas I would recommend using a batch macro!

Spoiler

[mceleavey]

Nice challenge. I actually had this exact same scenario on site with a client about 8 months ago, so I've already done this. I assume mine is the same method as the others, as a simple batch macro does the trick.

Spoiler

I started by identifying the requirement for a dynamic input tool, which would simply be the FullPath field as retrieved via the Directory tool.
I then used a text input tool to create a column called "FullPath" and converted it to a macro input on the right-click menu.
To build the batch macro I simply need to include a control parameter which instructs Alteryx to replace the value in the denoted field with the value being passed in to the macro, and then to simply apply a filter to the "FullPath" field. This will then run through the macro for each value returned. This is then fed into the "Dynamic Input" tool to import each file sequentially:



This macro just then needs to be used in the workflow, where a "Directory" tool is used to dynamically determine the "FullPath" field for all files. Remember to configure your batch macro input to map the input fields to the control parameter fields.



THERE IS ONE MORE STEP! 
Don't make my mistake and sit staring at your macro that keeps returning an error regarding the schema...remember to go to your view->Interface Designer page, select the spanner menu (I think wrench, for our American brethren), and select the "Auto configure by name" option.
You're ready to go.

[GalynaSkamrova]

Completed with the Community's help. Approach is quite the same as posted.

[SeanAdams]

Simple answer "Mr / Ms Client - please can you fix the sheet name"

More comprehensive answer below.

Spoiler

Similar to other folk like @patrick_digan - did a quick batch macro.

[josefmyers267]

Great challenge. I've never had to do this but after researching the community for solutions this is what I came up with:

STEP 1: Make a macro to combine the sheets.

STEP 2: Reference this macro in your second macro that reads all the sheet paths.

STEP 3: Reference the macro in STEP 2 to complete the project. 

See attached solution files.