Data Output file in desired format using File Brows and Action Tools

[anwerm2]

Hello -

I have designed a big workflow which will be posted on Alteryx Gallery for number of users. I am stuck up on the last part of the workflow where I want to allow users to save the output file in any of the following format: 

1. Microsoft Excel (*.xlsx)|*.xlsx|

2. Alteryx database (*.yxdb)|*.yxdb|

3. Comma-Delimited Text Files (*.csv)|*.CSV|

4. Tableau Hyper Data Extract (*.hyper)|*.hyper

I have updated the Arbitrary File Specification of File Browse tool with the above 4 option. It is working fine in Analytic App when user choice and file format of Output data tool - Configuration is the same. For example in the workflow file format is "Alteryx database (*yxdb) and in Analytic App I choose Excel then it is giving an error message "file format and file extension is different"

Is it possible to fix this through Action tool? I have attached the last part of my workflow. I would appreciate if any one help me to solve this problem as I spent a lot of time in completing the workflow.

Also how can I get a prompt for sheet name when saving in Excel.? Currently I am not getting a prompt for sheet in the workflow in Analytic App. Thank you in advance.

Regards

Muhammad Anwer

[mbarone · Accepted solution]

Hi @anwerm2 ,

If you change your action tool to "Update Output Data Tool (Default)", then it will prompt your user to navigate to a location, and specify the name and type of file.  For Excel, makes sure your users know to add three pipes after ".xlsx" to specify a sheet name.

For example:  "C:\Users\mbarone\Desktop\MyExcelOutput.xlsx|||Sheet1".

Other than that one caveat with Excel, it should work just fine (yxdb, hyper, csv, etc.).

[anwerm2]

Thanks Mbarone - oh it was so easy.

Majority of my users will use Excel for output. Is there any trick that instead of putting three ||| and sheet name user gets a prompt automatically for sheet name? I am just thinking  that it will be difficult to educate user.

Or any other solution

Thanks in advance.

Muhammad Anwer

[mbarone · Accepted solution]

Yes, you can do that, but it's a bit more complex.

You'll need connect your file browse to a condition tool.  The condition is Right([#1],3)='xls' OR Right([#1],4)='xlsx'.  Then you'll want to drop 2 action tools on the canvas.  Connect the T node of the condition tool to one action tool, and the F node to another (connect to the diamond nodes on the action tools).  Also, connect the file browse to both action tools (Q node to Q node).

Then, on the T node action tool, you want to use the "Update Value with Formula" option.  Make sure the "File - value='TestOut' line is highlighted.  In the Formula box, do [#1]+'|||Sheet1'.

On the F node action tool, choose your "Update Output Data Tool (Default)' option.

That should do it.

[anwerm2]

You are GREAT. Working fantastic. Thanks.

[mbarone]

Sure thing!