How I Cut 12 Hours of Spreadsheet Pain Into 12 Mins [7-Step Framework]

Read time - 5 mins

If you’re wasting hours with manual processes in Excel, read on.

Working with data can be a nightmare.

Especially poorly formatted data that you’ve been handed by someone else!

Without realising, we can spend hours re-formatting data. Then we get to the end of the day, and we feel like we’ve got nothing done.

Yes, if you’re an Excel pro, you can probably speed up the process a bit.

But do the rest of your team know how to do this?

Wouldn’t it be nice if you could do the work once, and then hand it off to someone else who can click a few buttons and get the work done in 60 seconds?

So I set myself a task…

Creating a Python Spreadsheet App

The scenario:

1. Supplier sends a CSV spreadsheet of poorly formatted data
2. Individual spends an hour re-formatting and tidying up the data
3. Very difficult to train other team members on how to follow the same process
   
   

The task:

1. Use AI to create the Python code
2. Automate the process
3. Create an app that anyone can use to achieve the same thing
   
   

The tools:

1. AI Chatbot
2. Python
3. Visual Studio Code
   
   

FYI - The AI model I used was GPT-4 at the time, and I used the Visual Studio Code desktop version.

If you can’t download applications, you can also use Google Colab, or GitHub Codespaces (VSC Online), you just might not be able to generate the app at the end.

Here’s the framework I used:

The Framework

1. Context

I always tend to start with a simple question to set the context.

2. Output Data

I then gave an example of the input data and and described what it was.

E.g:

[Example analymised data sample]

• Column L: County = County
• Column M: PostCode + PostCode
• Column N: Phone Number = Badly formatted phone number
• Column O: Mobile Phone Number = Badly formatted mobile phone number
• Column P: Turnover = Turnover
• Column Q: Sector = Sector
  
  

3.Required Output

I then described how I wanted the data to be transformed.

I used a series of if this, then that statements.

Here’s a snippet of a couple of columns:

• Column I : Persona = Job function distilled from the ‘Job Title’ column.

  • If the Job Title contains finance, financial, accounting or accountant > return ‘Finance’.
  • If the job title contains operations, purchasing, engineer, engineering or COO > return ‘Operations’.

• Column M: Phone Number = Reformatted data from raw data Column N

  • All number should be formatted as text to avoid syntax issues like 7.44E+09
  • All phone numbers should be output in the format +44123456789
  • There shall be no spaces in the numbers once re-formatted E.g 0123 456 789 will become 01234567890
  • There shall be no dashes in the numbers once re-formatted E.g +44123-456-789 will become +44123456789
    
    

Steps 2 and 3 are very similar to the ‘Define; step in my previous framework “How to Use AI to Code”. Where we’re documenting the before and after states.

I also wanted Sheet 1 to contain the re-formatted data, and Sheet 2 to be the raw data so I could compare.

4. Test

I then took the code and ran it in Visual Studio Code.

There was a bit of back and forth as when tested, some things were missing.

For example:

1. It was outputting 2 spreadsheets instead of 1 spreadsheet with 2 sheets.
2. It was populating some of my columns with data when I wanted some of them to be blank.
3. Plus, some of the code it produced was part complete.
   
   

5. Troubleshoot

It’s inevitable you get errors during testing.

So all I do is copy the error back into the chat.

You might need to do this a couple of times.

For me, most of my learning comes during troubleshooting.

6. Create the Interface

Once everything was working, it was time to package it into an app.

My conversation with AI was now LONG, so I knew I’d be getting issues when generating more code.

So I started a new chat conversation.

As per the above image, I gave my instructions on what I wanted the GUI (Graphical User Interface) to look like, and copied my previous code underneath.

AI chose ‘tkinter’ as the Python library to be used to create the interface.

7. Package it into an App

The last step was to package it into an app. So I asked AI for instructions on how to do this.

[NOTE - This code is run using the windows Command Prompt / PowerShell. If you’re not an admin on your machine, you won’t be able to do this.]

This is my final app.

I even modified it slightly to include a button at the bottom that links through to a SharePoint page giving the user step by step instructions on how to use it.

So there we have it.

You’ll be creating apps anyone can use in no time, and hopefully saving hours in the process.

Until next time.

Adam

P.S - Don’t forget to add [email protected] to your safe senders list 🙂