Clean Up a Messy Customer List with AI

Everybody has this file.

The same customer is in there three times with slightly different spellings. Phone numbers are written four different ways. Half the names are in ALL CAPS. There's a column somebody renamed and nobody remembers what it means. A hundred rows are missing an email.

Cleaning it by hand is a full day of work, so it never happens. And it keeps getting worse.

Twenty minutes with AI fixes most of it, and you don't need to know a single spreadsheet formula.

What you need

ChatGPT or Claude with the ability to upload a file. The free version usually handles small files. A big one may need the paid version.

Your list, saved as a CSV file. In Excel or Google Sheets, go to File, then Save As or Download, and pick CSV.

Before you start: make a copy

Warning: Make a copy of the file and work on the copy. Never do this to your only version.

This isn't because AI will delete things. It's because you might approve a change that turns out wrong, and you want the original sitting there when you realize it.

Step 1: Look before you touch

This is the step that separates people who fix their list from people who wreck it.

Upload the file and paste this:

CopyLook at this file and tell me everything wrong with it. Don't change anything yet.

Tell me:
- How many rows are exact duplicates
- Which columns have information written in different formats, and what those formats are
- How many blanks are in each column
- Anything that looks like a typing mistake
- Anything that looks out of place, like an email address in the phone column

Read what comes back carefully. People usually find out the file is broken in a way they didn't expect — a whole section of rows shifted over by one column, or a date field where half the entries are text.

If something surprises you, ask about it before you go further.

Step 2: Tell it exactly what to fix

Now you give instructions. Be specific. Change the list below to fit your file:

CopyFix these things and give me back a cleaned CSV file:

- Remove rows that are exactly identical
- If two rows have the same email but slightly different names, DO NOT delete either one. Put them in a separate list for me to look at.
- Make all phone numbers look like (555) 555-5555. If a number doesn't have 10 digits, don't guess — put it in the separate list.
- Change names from ALL CAPS to normal capitalization
- Leave blank fields blank. Do not fill in anything you don't have.

Then tell me how many rows you changed and how many you set aside for me.

Important: That line about not filling in blanks is the most important sentence in this playbook. Without it, AI will sometimes fill in missing information with something that looks plausible, because a complete-looking file seems like a better result. You'd end up with made-up phone numbers in your customer list and no way to tell which ones.

Step 3: Actually open the file

Download the cleaned file and open it.

Pick twenty rows at random and compare them against the original. Do the names match? Are the phone numbers the same digits, just formatted differently?

This takes five minutes. Do it anyway. You're about to load this into your customer system.

Step 4: Handle the pile it set aside

You'll have a second list of things it wasn't sure about. Near-duplicates. Phone numbers that don't look right.

Go through those yourself. This is the part that needs a human — you know that Bob Smith and Robert Smith at the same company are the same guy, and the AI doesn't.

It should be a short list. If it's huge, your file has a bigger problem and it's worth asking:

CopyThere are 400 rows in the set-aside pile. What's the common pattern? Is something systematically wrong with how this data was collected?

Step 5: Import it

Load the clean file into your customer system. Most systems will ask you to match up columns during import — that's normal, just take your time with it.

One more thing worth doing

While you've got it open:

CopyLooking at this cleaned list, tell me:
- How many of these people have I not contacted in over a year
- Are there any obvious groups I could sort these into
- What information am I missing that would actually be useful to have

That first question usually turns up a few hundred people who already knew you and forgot about you. That's the cheapest list of leads you'll ever have.

What to do next

Pick the people you haven't talked to in a year and send them one email. Not a sales pitch — just a note that you're still here and what you're doing now.

The list you just cleaned isn't worth anything sitting in a folder. It's worth something when you use it.