There is now an update to this post at: Alteryx: XSLX Wildcard inputs – read both to see how it comes together.
Wow, what a conference that was last week! I loved meeting all kinds of amazing customers in the Solutions Center and getting all kinds of product feedback, as well as being able to help people solve their problems. In particular, if you haven’t seen it, check out Adam’s Blog Macro Pack. He took a bunch of macros from this blog as well his and Chris Love’s and packaged them up with a cool installer so they show up in your tool palate.
Much of the feedback is already under consideration by product management and some of it has already been put on the development teams backlogs for Alteryx 9.1. There was one request in specific though that is actually much easier to implement as a macro then it would be as a native tool. The customer asks:
While I know that the input tool will accept a wildcard, it fails if the schemas are different. How do a read a set of files using a wildcard when the schemas don’t exactly match?
Batch macros in Alteryx already have this capability. In order to utilize that feature from the batch macro, it needs to read 1 file at a time. So how do I get the file list? That is just a Directory tool – it already has that feature.
So, if you just want the macro, you can download it here. Read on for more details on how it works.
How it works:
In order to do this and have it look like a built in tool, I am going to have to do a batch macro inside of a macro. The batch macro simply has a single control parameter that is the file name to read and then outputs the records. In order to allow the different schema’s to be read and merged, I have to go into the Batch Macro Properties and set the output mode – you find that by clicking on the little wrench in the interface designer. See right.
The next step is to build the enclosing macro. This is the one you actually will use. Using the new App Authoring tools in 9.0, I can quickly drag out the question I want to ask (there are only 3) and connect them to the tools that need to be changed with Actions.
In order to make it act like a built in tool, I include a few messages. There is one to output every file that is read to the output log. There is a second message tool set up to detect if there are no files to read and output an error. In order for the macro to act more like a built in tool, it is important for this error to appear before any secondary errors from the batch macro – there is a little bit of a trick to get a count of zero out as a record. See the module for details. This way if a wildcard doesn’t match any file, the user gets a nice clean error message.
Finally, there is 1 important checkbox to look at in the macro properties. “Output fields change…” This checkbox causes the Alteryx engine to cache the output fields for this macro as if it was a built in tool, instead of looking inside the macro and figuring out what its fields tools return. This is important because without running the batches there is no way to know what fields will come out. This does mean that to get accurate meta info for configuring downstream tools, you will have to run it once after configuring it. Just make sure to wait to save your module until after you run for the 1st time.
Once again, the package can be found here.
Thanks for reading,