Write Excel file to local drive from Gallery w/ Python?

Hello,

My Setup
Below is my Workflow, it lives in the company's Gallery:

1. It has a File Input which asks for an Excel file (some_file.xlsx)

2. it extracts some FileName information from the file (filename, sheetname, local path)
3. it passes both the data and the FileName information from the Excel file into the Python Tool

4. The Python Tool does some stuff to the data, creates a new Excel file, and writes it out as a new Excel file (some_new_file.xlsx).

My Issue

When I run this Workflow on my local machine, it runs fine, I get some_new_file.xlsx to appear in the folder where some_file.xlsx exists.

When I run this in the Gallery, I get nothing. I think it's writing out a file, but I have the suspicion that it is being written to some folder on the Gallery's physical server.

What I Would Like

I would like that the Python Tool either write some_new_file.xlsx directly to the same directory where some_file.xlsx was loaded from, or I would like the Gallery to prompt the user to download the results as an .xlsx file.

Is this possible with Python spitting out the Excel file?

Thanks,

Nick

Gallery

Output

Python

Accepted answers

apathetichell

That makes it more challenging - but not impossible. Here's what you'll need to do:

1) create enigne. workflow directory (ie the directory where you workflow is running) as a variable from your formula tool. include this in your python tool (this can be a #2 input)

2) feed the engine workflow into your output in python so you are writing your file to this location.

3) output file name & path (ie engine workflow directory) from the python tool.

4) connect the field containing name/path to a blob input tool. Alteryx will read in the python created file.

5) Blob output the new blob file. Gallery will prompt the user to download the output.

Note - you can skip steps 3/4/5 if you have a network share drive to write to...

All comments

apathetichell

The standard output tool will prompt the user to download the file as .xlsx when run on gallery. You don't need any python tool or any block until done. This should work out of the box.

You cannot write to your PC from gallery. This is true for python as well. Alteryx server has no access to your local machine for security reasons and just because "technology."

Nicholas_White

Hello,

Thank you for your reply, @apathetichell

My understanding of the Python tool is that any output from the anchors comes out as a dataframe, which can then be connected into the Output Tool, which is then configured to write out whatever file you need (Excel, CSV, to a database, etc.).

The issue I'm facing is that for my purposes, I need to write directly out from the Python tool to somewhere so as to not lose the file formatting that Python is doing for me (the Python code modifies the formatting of whatever Excel file I put into it). I'm using Python to format the file because Alteryx's reporting tools don't have the functionality for the kind of formatting I'm looking to do.

Are you saying that any Excel file created with the Python tool can be routed back into an Output Tool, and then Gallery's file download prompt will appear when complete?

Thanks,

Nick

apathetichell

That makes it more challenging - but not impossible. Here's what you'll need to do:

1) create enigne. workflow directory (ie the directory where you workflow is running) as a variable from your formula tool. include this in your python tool (this can be a #2 input)

2) feed the engine workflow into your output in python so you are writing your file to this location.

3) output file name & path (ie engine workflow directory) from the python tool.

4) connect the field containing name/path to a blob input tool. Alteryx will read in the python created file.

5) Blob output the new blob file. Gallery will prompt the user to download the output.

Note - you can skip steps 3/4/5 if you have a network share drive to write to...

Nicholas_White

@apathetichell ,

Wow, that worked!

The most difficult part was setting up the two Blob Tools, it wasn't cut and dry what I needed to do and where. After an hour or two of trial and error, I got Gallery to spit out the correct file and filename.

Thank you very much, I accepted your post as a solution.

Nick

apathetichell

@Nicholas_White- Glad you stuck with it - I've made similar workflows and yeah- the blob tools (especially setting up a template file and then changing the file) can be non-intuitive but they are super useful.

RenaFR

@Nicholas_White @apathetichell I'm stuck in similar situation, my python code is doing all the formatting and I want the same file as output when I run workflow on gallery. If possible can you please share the formula tool configuration?

Nicholas_White

@RenaFR ,

The Formula Tool configuration? Do you mean the Python + Blob Tool(s) configuration?

If I misunderstood the question, please rephrase it, and I'll do my best to get you a working Workflow.

Thanks,

Nick

RenaFR

Hi @Nicholas_White

Container 42 formula tool and summary tool configuration I mean.

If it is possible to share the working workflow it would also be helpful.

apathetichell

The workflow is really specific to what @Nicholas_White is building - I do not expect you'd have the same use case or schema as his files so this workflow would not work. Where are you seeing the errors?

The key parts are: get the filename that Alteryx is internally referring to your file as when loaded onto server. You will need to use the a system variable (workflow directory) to see the dynamically generated path on Server. Use this as the location to make your changes. Use the blob tool.

Nicholas_White

Hi @RenaFR ,

Please see attached workflow. It's a really stripped down version of my original workflow, I can't share the original because it contains sensitive information.

In a nutshell, I have two inputs to a Python tool: a filename input, and a raw_data input..

The Python tool brings in raw_data as a Pandas dataframe, writes the dataframe to an Excel file, and writes the Excel file back to disk using a modified version of filename as its name.

The Python tool also takes note of the path where the Excel file is stored on your disk, passes path back out to the Blob Tool.

The Blob tool collects the Excel file located at path and returns it to Alteryx.

When I have used this setup within an Analytic App, or when I run it in the Gallery, I get a prompt to open/download the Excel file.

Please let me know if this doesn't work for you.

Nick

blob_output.yxmd

AndreeaR

Hi Nick,

Thanks for sharing your workflow with us. I've been trying to export the results from the python tool to an excel file and this example was really helpful. I have an error though, it seems that the import of xlsxwriter does not work. Do you know if I need to have an active MS Office license for that? I'm running Alteryx on a virtual machine and the license is active on my local machine. Thanks!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703