Executing a Python script to format an Excel file generated by an Alteryx process

[terrence10]

I am trying to use a Python script to apply formatting to an excel file that was generated from an Alteryx process.

I want the Python script to execute after Alteryx generates the file. However, I'm not too sure how to link the two processes. 

I tried using the Python tool within Alteryx, but had problems linking to the output file, and installing the openpyxl package without admin rights. Since the file is also timestamped, I need to pass the filename to the linked Python process.

I have attached the sample Alteryx workflow, python script, and final output file example. I first run the Alteryx workflow, make modifications to the python script for the filename, then execute the python script (in Spyder) to generate the final file. I would like to run this "combined" process on our Alteryx Server.

Thank you for the help!

[DanielG]

@terrence10 - can you kick off the python process "After Successful Run" with some scripting?  I dont know Python so I can't give you specifics there, but maybe this will get you pointed in the right direction.  There is also a Run Command Tool you can use.

 

This is in the Events tab of the workflow configuration window.

[terrence10]

I did take a look at that option via this article here:

Run a Python Script in Alteryx

However, I was stuck on how to pass the "timestamp" filename to run - and how this would execute on the Alteryx Server. I wasn't able to successfully get the python script to run successfully via Events.

[DanielG]

Without knowing how exactly python operates, I would try to bring your python code in as a non-delimited text file which has a placeholder for the file name.  Then input the "code template text file" into the workflow then isolate the new file name in the regular part of the process and finally just do a find & replace to swap the placeholder in the python code with the file name/path.

Not sure how to help with the server.  Probably need to get your server admins involved.

[terrence10]

Hi DanielG - Thanks for the responses!

Yes, this could be an option to modify the python script with the "timestamp filename" within the Alteryx process itself.

I haven't done edits to a non-delimited text file within Alteryx before -- which tool option is that?

We'll see if anyone else in the community has ideas on the best way to combine Alteryx and python script run steps together.

Thanks again!

[DanielG]

@terrence10 Check this example out.  Use the basic Input Tool and when you pick the file from the Explorer dropdown in the Config window just fill it out like this (you can uncheck the box at the bottom which I forgot to do, as it pulled the first line of code in as column header in error).  

 Attached is a very basic example with some VBA test code I had been using in Excel recently (just to get something that is structured like I assume python resembles).  Also make your placeholder extremely bizarre so it would never actually be in your actual code in other spots and cause errors.  :)

[DanielG]

Also I should note once you boil down your information to get the file name from the new output, you should store the placeholder in a text file that you can append to the file name to mimic the text file I used for the sample because it wouldnt just be automatically there.  However if yours are just time-stamped, you could use the non-timestamped name as the placeholder and just swap that out for the timestamp name by creating a formula that extracts the base name from the time stamped name so it would be a bit more dynamic.  Hope that makes sense.  I am about to sign off for the night and this is a bit of a brain dump...  :)

[terrence10]

Thank you DanielG! This is a good way to "Pass in" the filename into the python script. I was able to replicate what you did using the Find Replace tool to bring in the timestamped filename 

If anyone has any idea how to execute this script after the workflow runs. I am trying the Events route -- executing Python.exe with the py script. However, I seem to be having issues now getting all the required packages installed (openpyxl) when executing python via command line. I think our organization restricts package installs via pip. I will also have to find out how python is installed on our Alteryx server, and if I could call python on the server.

Script will execute successfully in Spyder as packages are already installed in the Spyder (Anaconda) environment.

Thanks again!