Document Automation: Excel and XML

There's work that nobody contracts yet everyone needs: cleaning lists and converting documents. These two tools were born exactly from that need, and neither has anything brilliant about them: they solve a boring job, period. From the same family of small tools is the bridge between learning platforms.

The Excel duplicates

The first case: user lists in Excel with duplicates, since the same person had registered with the same email more than once. By hand it took hours; with pandas (a Python library for working with tables), one line. I made a small program with a graphical interface (windows and buttons, nothing terminal-based): you open the file, choose which columns you want to detect duplicates by —email, usually— and it generates the clean table. The first version worked, but the logic and the interface were mixed together and every change was an adventure. The second version properly separated the GUI functions and added a requirements.txt (the list of components that need to be installed), which is what allows anyone to run it without asking you a thing.

From XML to Word, with InDesign styles

The second tool has more history. I was working with a custom GPT that generated content in XML (a text format with tags), and that XML had to end up as a Word document with character and paragraph styles (the formatting tags) that InDesign, the layout program, would recognize when importing it. The key is a configuration file where each XML field is mapped to a style: on the left you choose the field and which style it goes to —whether paragraph or character—, and on the right you choose the files, the destination folder, and the name of the final document.

The first version uncovered a problem I hadn't anticipated: when the XML arrived with the fields in a different order, the result broke. I had to treat order as something you can never assume, and I added a specific path for files that came in unordered.

Packaging so anyone can run it

Writing the script (the program) was half the work; the other half was letting people who don't know what a virtual environment is (an isolated space with a project's libraries) run it. With PyInstaller, which packages everything inside an application, I compiled it, and to cover Mac with M1 chip and Intel Mac I set up a separate conda x86_64 environment to do cross-compilation (compiling for a different system than your own). Three specification files: M1, Intel, and Windows.

What I would do differently today

I would start with the configuration file and the validations, not the interface: validating the field order and the XML structure before converting should have been the first step, not a patch afterwards. And I would think about packaging from day one, because a tool that nobody other than you can run stops halfway. Still, between the two of them, a few weekly hours of work I never wanted to do again vanished.