A few people have been using the macro I wrote about in Alteryx: Wildcard Inputs, but have an issue with XLSX files.  The first thing to remember is that these macros I post (on my personal blog) are examples only and are not a supported part of the product.  I am happy to give people advice on how they might take what I did and extend it.  However, in this case, I thought it might make a good post about Alteryx macros with optional parameters, so I went ahead and did it anyway.

The problem with the macro as it stands is that it just browses files, but there is no way to specify a table name.  Reading from Excel (or Access or SQLite) gives an error that Alteryx doesn’t know what table to read from.  This is correct, because you haven’t told it what table to read from.

The 1st step is to add the new user interface.  This is simply a matter of dragging out the checkbox and text input tools from the interface tools category.  Once they are in the module, go to the interfaces view to put the textbox under the checkbox so it enables/disables with checking the box.

The next step is to make it actually do something.  This is a little tricky because, if the file does not have tables, we don’t want to add the table name.  I could have copied & pasted the whole macro and made a second one just for Excel files, but I would much rather have a single macro.  This is just the situation that the detour tools were designed for.  I put my formula tool that updates the path to include a table name between a detour and a detour end.  This way when the user of the macro is not reading from a file with tables, we can easily bypass adding the table name.

These changes were pretty small – it took me less than 20 minutes to modify the macro.  I hope this illustrates the flexibility of Alteryx in general and very specifically of the macro system.

The modified macro can be downloaded here.

Thanks for reading,

Ned.