We wanted to solve a common pain point when using Alteryx but don’t know how to code so we decided to use Chat GPT to help write Python code on our behalf. For how simple the resulting script is, it was fairly painful. It’s easy to imagine someone with python coding experience being able to knock something out in a matter of minutes, especially if they’re already familiar with the Excel libraries available to them.
The following technique is especially useful in a no code / low code world where you are often required to learn some type of coding to get the behavior that you want. It could be applied across many platforms to help develop internal web apps to low code SaaS solutions.
Italic print in the article is copied from Chat GPT
Step 1.) Figure out How to Install Python
This was pretty easy, while Python is installed with Alteryx we searched Google which brings up the most recent version of Python. Sadly, there’s no actual program to run it in a GUI, so you have to use Windows command line or Power Shell.
Q: How do you use Python?
There are several ways to use Python:
- Python Interpreter: You can use the Python interpreter to execute Python code line by line. This is useful for testing small snippets of code or for experimenting with the language. To start the Python interpreter, open a terminal or command prompt and type python.
- Scripts: You can write your Python code in a file with a .py extension and run it as a script. To run a script, open a terminal or command prompt, navigate to the directory where your script is located, and type python script_name.py.
- Integrated Development Environments (IDEs): IDEs provide a more feature-rich environment for writing and testing Python code. Some popular IDEs for Python include PyCharm, IDLE, and Visual Studio Code.
- Jupyter Notebook: Jupyter Notebook is an open-source web application that allows you to create and share documents that contain live code, equations, visualizations, and narrative text. Jupyter Notebook is a popular tool for data analysis and scientific computing.
Once you have a Python environment set up, you can start writing and executing your Python code. Python is a high-level, interpreted language that is easy to learn and use. There is a wealth of resources available online to help you get started with Python, including tutorials, forums, and documentation.
This part was straight forward enough. Some familiarity with computers is handy. We had Chat GPT write the script and then put it into a text file using notepad. You can turn on the file extension in Windows and rename it with .py at the end of the text file instead of .txt
Step 2.) Setup Python Path Enviromental Variable
This took an hour or so to troubleshoot as a combination of Chat GPT and Google. If it doesn’t work when you install it you can copy and paste error messages back into Chat GPT.
Chat GPT will suggest possible reasons for errors, and eventually figured out that it was because the Path Enviromental Variable wasn’t setup in Windows when Python was first installed.
Q: How do you Setup a Path Variable for Python? The process for setting up the PATH variable for Python depends on your operating system:
- Press Windows + X and select System.
- Go to Advanced System Settings.
- Click on Environment Variables.
- Under System Variables, find the Path variable and click on Edit.
- Click on New and add the path to your Python installation, such as C:\Python37\.
- Close all windows and restart the computer.
Once you have set up the PATH variable for Python, you can run the Python interpreter from any directory by typing python in the terminal. You can also run Python scripts by simply typing the name of the script at the command line.
Step 3.) Ask Chat GPT to Write a Script
This step took a couple of hours of copying and pasting Chat GPT generated scripts into Notepad and running the script. Prepare for a lot of patience and a lot of trial and error.
Whenever an error would occur, we would copy and paste the error into Chat GPT, get some suggestions and try to troubleshoot it.
It became fairly obvious, fairly quickly that typing in the correct question and being as exact as possible makes a massive difference.
Q: Can you write a python script to make all of the data in an excel column visible by adjusting the column width
Yes, you can write a Python script to make all of the data in an Excel column visible by adjusting the column width. Here’s an example using the openpyxl library:
pythonCopy code import openpyxl # Load the workbook workbook = openpyxl.load_workbook('data.xlsx') # Select the worksheet sheet = workbook['Sheet1'] # Iterate over all columns in the worksheetfor column in sheet.columns: max_length = 0# Iterate over all cells in the column column = column.column # Get the column letterfor cell in sheet[column]: try: # Necessary to avoid error on empty cellsif len(str(cell.value)) > max_length: max_length = len(cell.value) except TypeError: pass# Adjust the column width to match the maximum length adjusted_width = (max_length + 2) * 1.2 sheet.column_dimensions[column].width = adjusted_width # Save the workbook workbook.save('data.xlsx')
This script loads an Excel file data.xlsx, selects the first worksheet (Sheet1), and iterates over all columns in the worksheet. For each column, it finds the maximum length of the data in the column and adjusts the column width to match the maximum length. Finally, it saves the changes to the Excel file.
There are a lot of things that you don’t think about.
We quickly learned that we don’t know what we don’t know when trying to write this script. Here are some examples:
- What directory are the .xlsx files saved in?
- What directory is the script saved in?
- Which sheets of the workbook should the script run on?
- What happens if a column is blank?
- Which Python library do you want the script to use?
- How do you know if the Python Script successfully ran?
Each Time a Change Was Made it Broke Something Else
Part of the challenge of using Chat GPT to create a Python script in an iterative process is that it would sometimes needlessly re-write other parts of the script that were already working. You would fix one thing and it would break something else causing more trial and error to need to happen.
Step 4.) A successful Script
Finally, after a lot of trial and error Chat GPT generated a script that ran, didn’t pop out any errors and did not corrupt the Excel File. (This was a common problem even if the script ran without any errors)
Step 5.) Packaging a Python Script into an .exe
The last step was asking Chat GPT how to package a Python script as an .exe file. Which then involved a few follow-up questions like:
- How do you package a python script as an.exe file
- How do you install pyinstaller
- How do you use pyinstaller to create an .exe file?
Luckily Chat GPT walks you through it step by step and the creation of the .exe only takes a couple of minutes once you know what the python syntax is.
From beginning to end, the whole process took several hours. Much longer than a skilled programmer would take. However, Chat GPT made the process possible when it otherwise would have taken days or weeks to learn a programming language.
It’s faster if you already have the knowledge, but from a starting point of zero it’s an incredibly powerful tool.
Python Code for Reference
Here’s a copy of the finished Python Script for Excel Auto Sizer, a solution for re-sizing Excel columns when they’re output from Alteryx:
wb = openpyxl.load_workbook(file)
for sheet in wb.sheetnames:
ws = wb[sheet]
column_widths = 
for row in ws.iter_rows():
for i, cell in enumerate(row):
if len(column_widths) > i:
if len(str(cell.value)) > column_widths[i]:
column_widths[i] = len(str(cell.value))
for i, column_width in enumerate(column_widths):
col = ws.column_dimensions[openpyxl.utils.get_column_letter(i+1)]
col.width = column_width + 3
if __name__ == “__main__”:
for file in os.listdir():
print(“The columns have been resized successfully.”.format(file))