Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Example of how to allow user to browse for an Excel file and then load specific worksheet

PabloDanielovich
7 - Meteor

I'm a newbie, and so far I like a lot of things about Alteryx, but I'm missing something... something isn't clicking just yet.  I couldn't find an example that shows how to do this simple task (samples I did find were either too complex or too different from what I'm trying to do. 
All I want at this point is to allow the user to select an excel file (I'm using the File Browse tool with the Arbitrary File Specification of *.xlsx), and then trying to use the Input Data tool to open the sheet named "Master Table" in that file.  The Data Input tool shows the error "A file must be specified".  how do I pass the Path, Filename and Worksheet name to the Data Input tool?

13 REPLIES 13
SeanAdams
17 - Castor
17 - Castor

Hey Pablo,

 

@DanM and @TonyM have done some recent how-to guides on bringing in Excel sheet names.   Tony's one is below.

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/How-to-Input-Multiple-Sheets-from-Multiple-E...

 

Dan / Tony - is there a simple solution to getting the list of excel sheets into a drop-down for the user (per Pablo's question)?

PabloDanielovich
7 - Meteor

SeanAdams, thanks for the response.  I think your answer is more complex than necessary.  I'm still missing a piece of

some of very basic information. I just need the user to pick the filename, the worksheet name will always be "Master Table$"

Here's what I'm trying to do:

User is prompted for a filename.  I'm doing that with the File Browse tool.  Since I don't want to complicate things for them to have to wade through the different standard database formats, I'm using the "Arbitrary File Specification" option like this:

FileBrowseConfig.png

I have actually tried the "Standard Database File Formats" route as well, and I run into the same problem, the filename/sheet name don't get passed on to the Input Data tool.

Here's the latest try

 workflow1.png

So why then is the filename not passed on to the Input Data tool? 

and

How do I tell the Input Data tool: "Use the filename picked by user, BUT always open the worksheet named "Master Table$".  ?
I know I'm missing something very simple here, I'm a SQL and an Applications Developer so unfortunately I come with a more "sequential coding" type of mindset so that probably is keeping me from seeing things from the Alteryx perspective,  although I do SSIS which is very much like this.

Kanderson
10 - Fireball

One simple way is to assemble the file and sheet name via a formula tool and then hand it off to a dynamic input tool:

 

Capture.JPG

 

While the formula tool is not required I prefer to use it. Then you can select the following option on the dynamic input tool - 

 

Capture2.JPG

 

Good luck!

PabloDanielovich
7 - Meteor

This looks helpful KAnderson, could you please share a couple of screenshots with the configuration of the "update cell" control (action??) and the text input tool?   
We're on the right track here, but apparently I'm a bit thick, but I'm making progress. 

Kanderson
10 - Fireball

This should get you started, the files will need to be replaced with your own. This will thrown an error when there is no sheet named master table.

 

Good luck

 

PabloDanielovich
7 - Meteor
 
PabloDanielovich
7 - Meteor

Thank you so much, it runs without errors now, but then it says "there were no output files" 
What the???  (scratching head)
It worked fine without output files when I was using the plain Input Data tool and just hardcoding the filenames.  Why does it need to have an output file now?     If I add an output file at the end of the process, it works, so your response answered my original questions. 

Apparently I have a bit to go to understanding Alteryx in depth.  I love many of its features (currently replacing an Excel application that uses over 2200 lines of insane iterative spaghetti VBA code so this is soooo much nicer), but in some areas, I find it a bit counter-intuitive (or perhaps geared towards non-technical people or people that don't think like old programmers >>me (grin).)

I did figure out that I must run it as an analytic app if I want it to prompt for the files.  not sure why it works that way, but that's fine.
anyway thank you.

Kanderson
10 - Fireball

I saved this file as an analytics application. Apologies, I thought that was the desired format. I completely understand that Alteryx can take some getting used to. Could you tell me a little bit about your desired workflow. I know the user selects a file using a file input. We can do this via a macro and not an analytic app, and it should resolve some what you are seeing. What do you want to happen next?

PabloDanielovich
7 - Meteor

it's fine, I'm just so new to this I didn't know the difference between just running a workflow and an analytic application. I like the application. 
I'm basically working on automating a fairly complex recon process, so the input file names will change, that's why it's nice to just prompt the user.  I'm making progress on the project and have other challenges now (I'll be asking more questions). 

Labels