Cannot load in multiple xlsx files over wildcard

[Cela]

Hey everybody,

I am trying to load multiple xlsx files into my workflow. I just tried copying one of the files and saw that it grabbed the original as well as the copy so my assumption is that the difference of metadata might be the issue even though the structure of all files is the same on the surface.

Does anybody know a workaround for this?

Thank you in advance!

[binuacs]

@Cela @Try batch macro

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Knowledge-Base/The-Ultimate-Input-Data-Flo...

[caltang]

Yup, try using a Batch Macro in order to call all your files in. What matter is the content and the header names. If you're stuck, you can export your workflow here or sample data set(s) so that we may help further.

[Cela]

@binuacs @caltang Thanks guys! The header names are identical, the content (meaning the cell values) differ in some columns but that shouldn't cause an issue, the metadata differs, though. I will give the batch macro a go. Do you guys happen to have an exemplary batch macro for this kind of case at hand or can refer to one? It's completely fine if not, just wondering if I can start building on an already existing batch macro, otherwise I'll start from scratch.

[caltang]

The configuration of your Batch Macro can be different depending on the nature of your data. It is therefore a better option for you to create it or if you are comfortable sharing the data with us, then we can create something for you.

But re your point on the metadata, how different are we talking about? Is it a complete difference a la String vs Int16? Or String vs V_String?

[jdminton]

Hi Cela, If you are trying to bring the files in through an input tool, you could use a directory tool and filter to exclude the "copy" files and then dynamic filter the results into your workflow. It would eliminate any need for a macro.

[Cela]

@caltang the metadata can vary strongly, sometimes it's Double vs V_String and other times it's even Date vs V_String (most of the metadata is V_String luckily). I have built a macro to turn their metadata into a common structure but I didn't realize beforehand that the initial input was already an issue due to lack of actual input data at that time.

@jdminton Thank you for the recommendation! This would import the file paths, can you elaborate how this would eliminate the need for a macro? I would still need to write the files' contents into one common output. Thanks in advance!

[caltang · Accepted solution]

Wow! That is very different then. 

Please refer here for a guide on Batch Macro: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Knowledge-Base/CS-Macro-Dev-Reading-in-Mul...

Hope this helps somewhat!

-Cal

[jdminton]

The dynamic input tool will bring all of the files from the directory list into the workflow as one table. Example config for the dynamic input tool is below.

The input data source template is an input tool for one file, and the settings are used to bring in each file. The only requirement is that the fields and data types need to be the same for each file brought in so they match this template in the input.

The change in filename sounds like it would work for you. If the files were located in different folders, you would change Field to "Path" and change Action to "Change entire file path". 

Try it and share the workflow if you're having issues. We can help diagnose.

[jdminton]

Reading your comment to caltang though, it sounds like the data types are different. You can use the attached workflow after modifying to bring in all the fields from all files (and sheets). Set it up and if you run into issues, send us the workflow to review.