Analytics App - Input is Excel file with multiple sheets

KyleF

I have created a process that needs to have a user selected Excel file with multiple sheets (with the same schema but unknown number of sheets). My solution on desktop has always been to have the File Browse bring in "List of Sheets" and then use the dynamic input tool to run through that File Path and changing the sheet names. Once the process is turned into an App on the server, the server doesn't have access to that file path on its own, but rather "uploads" the specific file through the users permissions using the File Browse tool. The Dynamic Input tool does not have that same permission. It could be done if it would look to the server location that the server saved the initial Excel file, but I'm not sure how to either pull that file path or predict/designate what that file path would be. I've also thought of a batch macro, but I think that would run into the same permission issues as the dynamic input tool.

Have any of you encountered this? Have any of you found a solution or thought of a different solution? My only thought is to require the user to save the file in a location that the server has access, but that is somewhat burdensome and also opens up that network drive to a lot more activity and users.

Peachyco

I found that you can accomplish this with two Dynamic Input Tools.

1. Use the File Browser tool to take the Excel upload from user. But the user must choose the "List of Sheet Names" option. At the end of this step, you have a file path that looks like \\some address to the Excel file.xslx|||<List of Sheet Names>.

1.1. If you can't trust your user to choose this every time, then you have to catch their input and change the sheet name to "<List of Sheet Names>". I suggest using the Text Input Tool and a Formula Tool.

2. Pass the Excel file path to the first Dynamic Input Tool. Because the sheet name is "<List of Sheet Names>", Dynamic Input knows to return a table with two columns: File Name (the same one you passed from Step 1) and Sheet Name (the proper sheet names from the uploaded file).

3. Use a tool to create the proper file path for each sheet. This is as simple as using the Formula Tool and the REGEX_Replace formula to replace the "<List of Sheet Names>" from File Name with the value in the Sheet Name column. Your new file path/s should now look like \\some address to the Excel file.xslx|||Actual Name of the Worksheet.

4. Pass that new file path from Step (3) to a second Dynamic Input. Because it now has the Excel file name combined with a proper sheet name, it will start fetching the data in those sheets, instead of just the sheet names.

In this way, you don't need to know beforehand the temporary server location where Alteryx keeps the user-uploaded Excel file. The workflow automatically passes it along.