Change Input Tool directory based on user input. Import .XLS files with multiple sheets.

[271828]

I have a workflow that looks like this (followed by a bunch of data cleaning - this is dirty data). There are ~20 files in a given directory, each has 4 tabs with an identical schema. I've been playing around with Dropdowns and Actions, but I am struggling to make this more dynamic. I would like to take the [currently static] input directory and make it more dynamic by replacing a part of the file path from a user selected input. I.e. if this is the file path: \Documents\UTN Dashboard\Raw Data\FY20\2019.10\*.xls, I want to replace the 2019.10 with a different string.

Any suggestions?

[DavidP]

This is how I would do it. If the files don't all have the same schema, I would change the dynamic input tools for batch macros

[271828]

David, 

Thanks for the reply. I've downloaded your workflow and am about to test it out once my Designer updates to the latest version. 

One thing I forgot to mention is I'm dealing with .XLS files, which may create an issue with your answer (List of Sheet Names). In my experience so far, .XLS files don't play very well with Alteryx. That's why I created a rather basic workflow to import them - despite reading almost every XLS input question I could find, there doesn't seem to a be more elegant solution that is easy to implement.

[DavidP]

Ah yes, xls files are troublesome, so you won't be able to use the list of sheet names idea.

Give me a few minutes and I'll do up a version that will work with xls files.

Incidentally, if you open the alteryx workflow file in a text editor, one of the 1st lines is the version. you can change it to your version, save, and then open it in Alteryx.

[DavidP · Accepted solution]

Something like this should work for xls files. 

Download the attached workflow package file, change the extension to zip and unzip the file. Now edit the Alteryx App file in a text editor and change the version

You have to pre-populate the text input tool with your sheet names. You'll then have to change the template file in the dynamic input tool.

[271828]

Thanks David. That works well. I had to modify the dynamic input to grab the FullPath field instead, and the Append tool has a warning re: >16 source records. Sounds like that's an Alteryx feature rather than an actual runtime error though.

[DavidP]

Perhaps I had the source and target on the Append tool the wrong way round. See if you still get the warning if you switch it around.

[271828]

EDIT:

I neglected to look at the record count earlier. When the sheet names are the Target and Directory is the source, it skips the last 3 files in the directory for some reason (returns 68 total records instead of 80). 

There's no error when I switch it around, but it takes about 5x longer to run. 58.2s vs 12.8s. Output is the same. I'll gladly trade a longer run time for a trivial error message.