Simple AI Prompt Generated Python Script to Clean Up an Excel File

Like most companies, we are trying to get better at using AI, and especially creating small AI prompt generated Python scripts to automate simple tasks that can take time. Also like most companies, we have different software tools that don’t really create reports in a format we find useful. One such area for us in our timekeeping tool that is part of our HR management (HRM) system that also does our payroll. It outputs the information, just not in a way we want it. And their timekeeping module doesn’t really have a full API. So we are stuck outputting an Excel file, editing them, then pasting things together.

It’s not a huge problem, generating the reports takes longer than doing the manipulation. But, as an engineer, every time I did it I got frustrated and said to myself, “Some day I’m going to write a Python script for this.”

Who am I kidding? Yes, back in the day when I was doing engineering all the time, I wrote a lot of code. But the truth is I just don’t do much anymore. Then, this morning, I said to myself, “Today I’m going to use AI to write a Python script for this.”

Many of us here at PADT are working on integrating AI into our daily tasks to get better information faster, or to speed up tasks. It took me about ten minutes to get a nice little script that will save me about one minutes every Monday. In ten weeks, I’ll see a return on my investment.

Why I Needed an AI Prompt Generated Python Script: JobList

This is a simple problem. The tool we use to review the hours employees spend on different charge numbers combines data from our HRM’s Timekeeping module and our accounting system. The HRM won’t output all the data about our projects. It will only output the full name of a project, and not the JobID. And we need the JobID to look up additional information in our accounting system. So, we need a simple lookup table that lists the JobName and the JobID.

Simple, except there is no way to just output that data. The HRM creates Microsoft Excel files as .xlsx or .csv depending on the module. The report that contains the JobName and JobID has a lot of other stuff, and it’s formatted for reading, not data processing. For a long time now, I’ve been doing the following every time I need to update our Timekeeping spreadsheet:

1. Open the report in Microsoft Excel
2. Delete a bunch of rows
3. Move a column
4. Delete the columns
5. Sort by the first column

Simple. Now I have my table. On a bad day it can take me about two minutes, but if I’m focused I can get it done in less than one. No biggy. But it bugged me.

I Need to Get Better at Creating AI Prompt Generated Python Scripts Anyway

The issue is not that I don’t know how to write code, it’s that I don’t do it enough to be fast at it and I just don’t have the time. Here I am with a simple algorithm and access to the Microsoft 365 version of CoPilot, and ten minutes to figure this out.

Here are the prompts I used. The first part is just the manual steps I went through every time I modified the file.

• Make a script that reads an excel file
• here is the file: ERM M_T_T – Labor Level Details (13).xls” (file upload)
• Delete all rows up to and including the row with ‘Project Number’ in the first column of the row
  Delete the next two rows
  Remove all blank columns and rows show me the results”
• Swap the first and second column
  Remove columns 3 and higher”
• Sort by the first column Output the number of rows remaining Save to ‘joblist.xlsx'”
• Remove all rows with a blank first column
• Remove the header row
• at the start of the program, use QT to prompt me for the name of the input file.
  Also use QT to give me the count of rows after all the processing
  • I tested the code here. And it worked! But when I ran it a second time it crased because my file already existed. So one more prompt.
• update the code so that when it saves to ‘joblist.xlsx’ if the file already exists, it adds _0n where n is an integer until it finds no existing file

It was showing me results after each prompt until I added the part about using QT to prompt for the file. QT is a Python GUI library, and CoPilot can’t run Python with GUIs. So, I copied and pasted it into Microsoft Visual Code, where I could debug it if needed. I didn’t have to.

In Ten Minutes, I had my AI Prompt Generated Python Script

I would share the results of the little script, but it includes customer names, and sometimes that is proprietary. You will just have to use your imagination.

But I can share the code it generated:

#---------------------------------------------------------------------------------------
# joblister.py
#
# Simple script to convert the Jobdata from PADT's HRM into a two column 
# table of just the JobID and JobName and remove all non-job information
#
# Input: Prompts for file generated by HRM
# Ouput: Saves converted informatino to joblist_nn.xlsx in the current directory
#             Lists the number of rows and the filename the data was saved to
#
# 7/21/2025, Eric Miller, PADT
# Generated entirely in M365 Copilot
#---------------------------------------------------------------------------------------
import sys
import os
import pandas as pd
from PyQt5.QtWidgets import QApplication, QFileDialog, QMessageBox

def get_unique_filename(base_name="joblist.xlsx"):
    if not os.path.exists(base_name):
        return base_name
    name, ext = os.path.splitext(base_name)
    i = 1
    while True:
        new_name = f"{name}_{i:02d}{ext}"
        if not os.path.exists(new_name):
            return new_name
        i += 1

def process_excel(file_path):
    df = pd.read_excel(file_path, engine='xlrd')

    # Remove rows up to and including "Project Number", plus next two rows
    target_index = df[df.iloc[:, 0].astype(str).str.contains("Project Number", na=False)].index
    if not target_index.empty:
        df = df.iloc[target_index[0] + 3:]

    # Remove blank rows and columns
    df.dropna(axis=0, how='all', inplace=True)
    df.dropna(axis=1, how='all', inplace=True)

    # Swap first and second columns
    cols = df.columns.tolist()
    if len(cols) >= 2:
        cols[0], cols[1] = cols[1], cols[0]
        df = df[cols]

    # Keep only first two columns
    df = df.iloc[:, :2]

    # Remove rows with blank first column
    df = df[df.iloc[:, 0].notna()]

    # Remove header row
    df = df.iloc[1:]

    # Save to a unique filename
    output_file = get_unique_filename("joblist.xlsx")
    df.to_excel(output_file, index=False, header=False)

    return len(df), output_file

def main():
    app = QApplication(sys.argv)
    file_dialog = QFileDialog()
    file_path, _ = file_dialog.getOpenFileName(None, "Select Excel File", "", "Excel Files (*.xls *.xlsx)")

    if file_path:
        row_count, saved_file = process_excel(file_path)
        QMessageBox.information(None, "Processing Complete", f"Rows remaining: {row_count}\nSaved as: {saved_file}")
    else:
        QMessageBox.warning(None, "No File Selected", "Please select a valid Excel file.")

if __name__ == "__main__":
    main()

Since it reads a specific file from our named HRM, it’s not directly usable, but someone might modify it for their own use. So, here is a link to the code on PADT’s github: github.com/padtinc/joblister

It did an OK job of commenting, but not as good as I’d like. I know that other Generative AI tools do a better job of creating clean code. But this was good enough for me. The header is kind of our standard header. I could have probably prompted for that, but I just typed it in.

Simple Is Often the Best Way to Get Started

This is kind of a trivial example, which is why I did it. Over the years, when learning a new, complex tool, I’ve found out the hard way that starting with almost trivial applications of the tool helps me get my head around how things work.

Hopefully you have found it useful.

I’ll also mention that PADT does write real code for more complex things, with and without AI’s help. Most of our programming work is around customizing simulation workflows, connecting simulation tools, adding functionality to engineering software, custom simulation and design applications, automating engineering tasks, and writing embedded code for control systems. If you need any help with any of those things or want to learn more about our simulation consulting, product development, or 3D Printing services, reach out!

Note on the Artwork used for the Hero Image

That cool “advertising” was also created by generative AI. I used Adobe Firefly with the Imagen 4 model and the prompt:

• an older engineer with gray hair and a beard sitting at a desk with a computer. he is using generative ai to write a small piece of python code for him. make it in the style of a 1920’s advertisement

I started to think about how to tweak it, then realized it is perfect the way it is: