Summary
- I coded and distributed a small utility to automate a repetitive data entry and reporting task for the customer service department.
- I used Python, the Tkinter library for a user interface, and Pandas for data manipulation.
- The utility saves approximately four hours of manual data manipulation and reporting per week.
Background
As is common manufacturing, customers often require regular updates on a manufacturers' planned delivery dates. This helps validate their own planning and make adjustment for any delays in their supply chain.
The need for this particular utility was born from a transaction where a customer would regularly send a spreadsheet to their customer account manager at our company asking it be updated with changes to our manufacturing schedule. This boring, data-entry task would take the account manager up to half a day every time the update was requested.
Not all data manipulation workflows are good candidates for automation. For a process to be straightforward to automate, I typically look for the following two characteristics:
- Self contained inputs and outputs. This means that most if not all of the data should be accessible to the utility. After all, we're trying to eliminate the need for human input.
- Regular data structures on both ends of the process. The format of the incoming data should be the same every time otherwise the utility has to deal with conditionals and contingencies checking type formats and doing conversions. Likewise customizing the output format also adds complexity and therefore code and time.
This process met the requirements for automation and I offered to give it.
The App
The goal of the utility was to take the customer-provided spreadsheet, the planned ship date sheet from our system and, with some customization inputs, produce an updated copy of the customers' report to be sent back to the customer.
The primary user of the utility would be the account manager.
Another aspect to look for when identifying opportunities for automation is a user who both knows their process well and has some idea of the workflow they would like to end up with using your utility. Nothing is more difficult than trying to satisfy a user who doesn't understand the process well enough to describe the input and output requirements to you or who has such an active imagination that they immediately think of hundreds of additional requirements for the utility to satisfy.
In this case, my customer, the account manager, had a clear understanding of the process, was able to describe the output requirements to me as an outsider, and he had a good idea of the workflow he wanted to end up with.
User Interface
Since most users are not comfortable with command-line utilities or editing file paths in a script file, I typically build a simple graphical user interface with my utilities. In this case I chose the built in GUI library Tkinter which comes bundled with Python.
I've found Tkinter works fine for these simple interfaces although it leaves something to be desired in terms of visuals.
Working with Excel
Pandas is an amazing library for manipulating and analyzing data. It is certainly overkill for this application but since I was familiar with Pandas I decided to use it to import, manipulate, and export the data back into the updated report.
Distribution
Getting Python code to run on a computer that doesn't have python installed can be problematic. For me distributing software inside the company I work for has been best accomplished with a combination of Github and PyInstaller. PyInstaller is a command-line utility that bundles a Python application with its dependencies and the python runtime into a single package allowing it to run on a computer without Python installed.
So to distribute the utility to the customer accounts team, I compiled and uploaded a release to Github and shared the link to download the executable to the team.
You can find the code on my GitHub and thank you for reading!
Lessons Learned
Make friends with your IT department
At least in my experience, IT isn't typically excited when executables that were downloaded from the internet run unchecked on company equipment. However, making the case for allowing the use of small, time-saving, utilities for excel manipulation is a lot easier if you have a good relationship with your administrators.
Function (mostly) over Form
Tkinter looks as though it was styled for a 2000's era system but... if your app accomplishes its task then the visuals can generally come second. In the future I plan to take a look some of the ways that Tkinter can be 'themed' to improve its appearance. In addition I've read about Kivy as an complete alternative to Tkinter and will be looking to try it in a future project.