Can't get all sheet names when one contains trailing blanks

[apozzy]

I have what should be a simple issue, but I just can't crack it. I've seen a few similar posts, but none have quite the same situation or a solution I can make work. I have multiple files I need to read, and the sheet names should be the same, but in some cases the sheet name contains a trailing space. It appears that when Alteryx reads this space in, it stops the import and I can't get all my data. I've tried directory input, a batch input routine, but it always bombs.

I believe that if I can simply get a simple input tool to read in all the sheets that I can make it work, but I can't do even that. Here is a picture of a simple workflow pulling from the three files, showing the 3 records I need:

If I use a wildcard for the input, then I only get 2 of my 3 files - the "8 IE" file which has a space in the sheet name is ignored.

 

How can I pull in all 3 of these files at the same time without error? This has to be possible, but I've just burned most of a full day trying and still can't get it. Thanks!

[binuacs]

@apozzy Use bath macro or dynamic input tool for your use case

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

[apozzy]

Neither of those options works, for the same reason, that's why I just did the simple input here. If I can't read the sheet name with the spaces then I can't run a batch macro or use dynamic input, as the same problem occurs. If I remove the one file with the space in the sheet name then both dynamic input or a batch macro work great, but nothing works when it's there. As soon as Alteryx hits the sheet with the extra space in it, it seems to stop, no files after are being read, no matter how I read it.

[caltang]

Try using a Directoy Tool and include sub-directories, choose the file that you want also. That will give you the file path.

Now, have a Text-Input Tool with the list of Sheet Names that you know, either Join or Append to the Directory Tool results, then use a Batch Macro/Dynamic Input tool from there as per usual. That should do the trick.

Otherwise, can you provide the sample data set please?

[Peachyco]

Could it be a version issue? I'm running Version: 2023.1.1.247 Patch: 2, and I can input sheets with trailing whitespace in their name (e.g. "Sheet3 $").

 

Or maybe there's something else wrong with that sheet other than the name?

EDIT: I also tried to import all sheet names in the Input Data Tool and passing them as parameters to the Dynamic Input Tool, and it still works:

 

[apozzy]

My apologies, I didn't realize I forgot to include the workflow the first time!

I had already tried what you describe, but I can't get anything to return both the sheets with as well as the sheets without the spaces, so nothing downstream from that will work, I have nothing to join to. Attached is the dataset. Thanks!

[apozzy]

Perhaps it is a version issue, for some reason we've been told not to upgrade at the corporate level. I didn't realize I'd forgotten to attach my original workflow - is there any chance you could run this quickly to see if it's also fine, in which case I know the version must be the problem? Thank you!

[Peachyco]

@apozzy Checked your workflow on my machine and it worked just fine.