Input tool with wildcard not pulling in all files

Question

My input tool is setup configured like this

Within the folder above there are 24 subfolders, each with a different name. Within each of these 24 subfolders is a file called Contactlist.xlsx

When the workflow runs its not pulling in all of the files, only about 15 of them.

Some have a different number of columns so I can understand why those were left out.

But with others the files are identical in columns, column name, formatting, etc.

The only difference being the actual names and emails in the columns.

I have tried copying one of the lists to a different folder with no luck.

I tried a different method as well. I used a directory tool and a dynamic input tool. This gave similar results, but this time telling me that the files with different number of columns couldn't be included, but others, that the schema? was different.

How can I fix this? My last resort is to try recreating all contactlist files to be 100% certain everything is the same and uniform.

Thanks

__PRESENT

NeoInfiniTech · Answer

Hi @LBCoEnterprises,

The reason why you are encountering this issue for the sheets that have identical column structure may be due to one of the following:

1. A string column (for example Name) may contain Unicode character (i.e. Extended Latin or non-English alphabet) in a given sheet, resulting in a V_WString (Unicode supported) column, while another sheet may not contain names with any Unicode characters, which would result in a V_String column, which happens to be incompatible with V_WString when reading them in with wildcard.

2. Same goes for a column where it might be null for a file completely (resulting in a non-string data type), while in a different sheet it might not be entirely null (for example a Notes column), which would result in a String column and therefore cause errors.

3. Another scenario is that there might be a phone number column where it is not formatted but rather written as numeric in Excel (i.e. 1234567890 instead of (123)-456-7890) but it is formatted as expected in another sheet, therefore causing an error.

4. Date and amount columns with different formats across the sheets (not recognized as date or number natively in Excel but rather directly copied and pasted from a CSV file without being formatted correctly for some files).

As you can see, the list goes on, but the most likely scenarios you might be encountering is number 1 and 2, although I believe it is worth noting the scenarios 3 and 4 as they are also likely to be encountered where the data might not be as ideal as expected.

I would like to recommend you a macro of my own, which is named CSV Friendly Multi-Input (Inspired by CReW Wildcard Input) (compatible with Alteryx Designer 2023.1 and higher). It allows for fine tuning the sheet selection among many more options.

While the macro linked by @alexnajm is also very useful when reading in files with different schema and may be a solution for this case, I hope that this macro will also be of use for those encountering both similar and more complex scenarios in the future.

alexnajm · Answer

Use a batch macro! You can download it from here: https://knowledge.alteryx.com/index/s/article/The-Ultimate-Input-Data-Flowchart-1583459854309