Using the Wildcard newb help

Question

Hi,

Extremely new to Alteryx so sorry if this is on the wrong forum!

I have an issue whereby when I look to stack 3 files on-top of each other using the wildcard option it is only bringing back the first file in my folder location.

The output table suggests that all 3 files are being read but then only records from 1 of the files (8408 total) is being returned. I have tried with all 3 files and removed the wildcard; all files import successfully in isolation, but not together.

Any guidance would be much appreciated!

Michael

Lewis_Data · Answer

Hi Nath, sorry for the delayed reply! This project has been on the backburner until now.

I have pulled the batch macro in as you have advised but the batch is now only reading the first of the 28 files I need to stack due to a 'schema error'. I'm not sure if I've pulled the batch macro together correctly or not so the files stack?

Thanks!

Print Screen.PNG

DataNath · Answer

Thanks a lot for that @Lewis_Data - really useful! From a quick investigation it looks like the error is coming from some of your data being completely null, which Alteryx defaults to double - more of an explanation on a recent case of this I noticed here: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamic-Input-Multiple-Sheets-On-One-Excel-File-Null-Fields/td-p/1000480

I included the attached 'Field info' workflow so you can see for yourself - although the names and order are all the same, the data type changes throughout which also constitutes a schema change which will cause an error if you try and use a wildcard. So what's the fix here? Well, whenever we run into different schemas, the play is to use a batch macro instead. Much like in the fix I linked above, here we just force all fields into a standard format (strings in this example) - if you do a bit more investigating and find it's only a few offending fields causing this then you only need to change them, but to be safe with the limited time I have, I just made every single field a string:

That way, when they come out of the batch macro they can all union just fine.

In terms of the outer workflow itself, I've just fed the control parameter the fullpath of each file, which I have retrieved from the Directory tool where I saved them:

Now when I run the workflow, Alteryx can happily bring in all 3 files and stack them as we wish. The only thing we need to do now (annoyingly, hence why I mentioned trying to find only offending fields earlier, so that you don't have to do this more than you need to) is change all of the fields back to the data type they should be when they come out of the batch macro - just use the select afterwards to do so:

Really hope this helps. Please let us know if you need more assistance - have attached everything mentioned here so you can use it as a reference/use the macro.

In the macro, you'll also just need to change the Input Data location etc to your own:

As well as the filepath part of the Input Data string within the Action tool:

Get around schema change.yxzp

Field info.yxmd

Lewis_Data · Answer

Thanks Nathan, I have anonymised the 3 files and attached below. Also included print screen of the process I have followed.

In the final upload 1 & 3 dropped in but file 2 is not doing so. I have attempted to format all columns the same but still no look. The reports out straight out of a system and I would prefer not to touch before collating!

Appreciate your support.

Michael

1.xlsx

2.xlsx

3.xlsx