Hi
I have an analytic app workflow in which the user is prompted to select a Data Input source file (an Excel workbook) using the File Browse tool. An action tool then passes the filename to a Data Input tool using the Update Value with Formula option, where the formula passes in the filename, but has a hard-coded worksheet name. This workflow runs just fine in Designer. However, when I upload it to a Gallery there are a number of issues:
1. When a user runs the workflow, as expected they are prompted to browse and select a file. In the file browse window the file type or extension options configured in the File Browse tool are ignored (not a big deal, but different to the Designer experience).
2. Upon selecting an Excel workbook the user is then prompted to select a worksheet from the chosen file.
3. Finally, when run the job fails with an error saying
File not found "D:\ProgramData\Alteryx\Service\Staging\8696_4f022fb2f2454d5cb168e5d9000ff581\In_5b1733b05fcdd221f8006d00\MyFile.xlsx|`Sheet1$`" (Tool Id: 2)
I have seen a similar post with this same issue, where the response was that this is a bug and that it should work just as it does in Designer. That was a year old however, so I'm hoping there has been a fix or that there's a workaround I can use.
Thanks!
@BrettLuckman What version of the desktop and gallery are you running? Can you post a dummy workflow so I can try to replicate on my end? In my experience, the file browse tool will force you to select a sheet, so something like this formula in your action tool may work:
regex_replace([#1],"\|\|\|.*","|||Sheet1")
where #1 is your connected Filebrowse.
Still no luck with this one. The Gallery still prompts the user to select a worksheet name after browsing for an Excel file to update the Input Data tool. It does this whether I use 'Update Value' or 'Update Value With Formula'.
I've attached an example of part of this workflow that is behaving like this. Note that it works fine and as intended in Designer, but the Gallery experience is different.
Is that D-drive on the error message coming from the user's run of the app? Or is it coming when you debug it in Designer?
Hi @mbarone
The error message is what's returned when a user runs the workflow from the Gallery. I'd assume that the D: drive location is a temporary staging location that the server uses when running the workflow right?
Thanks,
Brett
Yes, that sounds right - it would be the staging area the server uses. Does the user have access to the server? If not is the app and/or Gallery running on credentials that does have access to the server?
The Gallery uses a server user ID so has full access. The main issue I was hoping to get some clarification on here is whether there's an option to have the user select just a file and not have to select a worksheet.
Hi @BrettLuckman,
Currently with Excel files there's not a way that I am aware of for the user to specify a XLSX file without selecting a sheet when it comes to the Gallery. The Gallery handles the File Browse tools a bit differently, since you have to upload the file to the machine. In many use cases that I've used this particular tool with an Excel document on the Gallery, I've needed the user to be able to select a sheet dynamically which is likely why it was implemented in this manner. Otherwise, it's likely if you wanted to have the ability for a user to select a sheet you'd have to setup a complicated chained application in order to select a sheet name for an input.
If you'd like to see this as a feature (maybe an option to turn on or off sheet selection?), I might recommend posting it to our ideas board here:
https://community.alteryx.com/t5/Alteryx-Server-Ideas/idb-p/server-ideas
As previously mentioned here by @patrick_digan, RegEx or other replacement options would allow you to replace the value regardless of the selection with a specific sheet if you wanted to "hard code" the sheet that the workflow processes in the background.
No, there is no way to not have it prompt them for a sheet name. At least no way that I know of.