Clean a messy CSV with AI: a worked example you can check in Google Sheets

AI can help find inconsistent labels, repeated records and invalid numbers in a spreadsheet. To get a useful result, first tell it what each row means and which changes are allowed. Keep the original file, review the proposed changes, then check the cleaned result separately.

This guide takes an eight-row workshop-booking file through that process. You will finish with a cleaned table, a change log, an exception list and a provisional seat total you can reproduce yourself.

Practice data: Every booking below is fictional. The example contains no real student or customer information.

What you need

• An AI assistant that can read pasted tables or CSV files
• A spreadsheet application for checking the result
• A copy of your source data that you can leave untouched

ChatGPT supports CSV analysis and can run Python calculations for some analysis tasks. Capabilities depend on the account, model and workspace. When code is used, review its assumptions and outputs. These prompts also describe the task in ordinary language, so you can adapt them to another assistant. OpenAI: Data analysis with ChatGPT

For real work, start with fictional examples or an approved, minimized dataset. Removing names alone may not make a file anonymous. Do not upload student records, client details or confidential information without permission and an appropriate organizational policy. Review the service's privacy and retention settings first. In ChatGPT, opting out of model training does not delete saved chats. OpenAI: Data controls

1. Define what a row represents

Our file records workshop bookings. A booking can reserve more than one seat, so counting rows will not tell us how many seats are reserved.

For this exercise, use these rules:

• One accepted row represents one booking.
• The booking ID identifies the booking. Repeated IDs need review.
• The only allowed topics are “AI Basics” and “Prompt Writing.” Capitalization and ordinary outer spaces do not change their meaning here.
• Seats must be a positive whole number.
• Unknown values stay unresolved until the source owner explains them.

These are rules for this dataset. In a different file, zero seats might mean a cancellation, and a repeated ID might mean an amendment. Decide that before cleaning.

Save this sample as a plain-text CSV, or paste it into your assistant. Keep an unchanged copy called “Raw.” Record numbers in the instructions below start at 1 after the header.

booking_id,topic,seats
B001,AI Basics,2
B002, ai basics ,1
B003,Prompt Writing,3
B003,Prompt Writing,3
B004,AI BASICS,2
B005,Prompt Writing,two
B006,AI Basics,0
B007,Prompt Writing,1

2. Ask AI to inspect before changing anything

Use this prompt with the sample:

Inspect this CSV without changing it. One row should represent one booking, and booking_id is the booking key. Allowed topics are AI Basics and Prompt Writing; capitalization and ordinary outer spaces are formatting differences. Seats must be a positive whole number. Number source records from 1, excluding the header. Report the row count, repeated IDs, topic formatting differences and invalid seat values. For each issue, give its source record number, original value and a proposed next step. Do not infer missing information, convert words to numbers or remove repeated records yet. Ask the questions needed before cleaning.

A complete inspection should identify:

• Eight source records
• B003 appearing in records 3 and 4
• Topic formatting differences in records 2 and 5
• “two” in record 6, which does not match the required numeric format
• Zero seats in record 7, which fails the exercise's positive-number rule

These are expected findings from the sample, not a transcript of a chatbot test. If your assistant misses one, point to the record and ask it to inspect the full file again.

Do not use an automatic “remove duplicates” action before deciding which columns define a duplicate. Google Sheets lets you choose the comparison columns, and its duplicate-removal behavior treats some differences in case or formatting as equivalent. Google: Remove duplicates and trim whitespace

3. Make the ambiguous decisions explicit

For this fictional exercise, the source owner supplies three answers:

1. Record 4 is an accidental duplicate of record 3. Keep record 3.
2. B005 should have two seats. Correct the text “two” to the number 2.
3. B006's zero is unexplained. Keep the record in an exception list and leave it out of the provisional seat total.

That last decision matters. Excluding an unresolved record does not establish that the booking was cancelled. The report must continue to say that it is incomplete.

Now ask the assistant to apply only those decisions:

Create a new cleaned CSV and leave the original unchanged. Normalize the topic labels to the two allowed spellings. The fictional source owner has confirmed that source record 4 is a duplicate of record 3 and that B005 in record 6 has 2 seats. Exclude source record 7, B006, from the provisional result because its zero seats remain unresolved. Make no other corrections. Return: (1) the cleaned CSV, (2) a change log with source record numbers and old/new values, (3) an excluded-record list with reasons, and (4) accepted booking count and seat totals by topic. Reconcile every original record to either the cleaned CSV or the excluded-record list. If you execute code, show the code and its output; otherwise label the results as unexecuted calculations.

The approved cleaned CSV should be:

booking_id,topic,seats
B001,AI Basics,2
B002,AI Basics,1
B003,Prompt Writing,3
B004,AI Basics,2
B005,Prompt Writing,2
B007,Prompt Writing,1

The change log should contain three cell changes: the topic in records 2 and 5, and the seat value in record 6. The excluded-record list should contain record 4, the confirmed duplicate, and record 7, the unresolved zero-seat booking. Preserve their original values and reasons.

For a large file, keep the original record number beside each transformed row in an audit copy. That makes it possible to trace a surprising result back to its source.

4. Check the result independently

Open the approved cleaned CSV in a blank Google Sheet. The header should occupy A1:C1 and the six bookings A2:C7. If pasted text lands in one column, use Data → Split text to columns and select a comma separator. Google: Split text into columns

Put these checks outside the data range:

COUNTA counts values rather than distinct bookings, so it needs the duplicate check below. Google documents SUM for adding values and SUMIF for adding values that match a criterion. COUNTA documentation, SUM documentation, SUMIF documentation

For a duplicate check, put =COUNTIF($A$2:$A$7,A2) in D2 and fill it down to D7. Every result should be 1. COUNTIF is case-insensitive, so this check is appropriate for our consistently formatted sample IDs. A system with case-sensitive identifiers needs a different comparison. Google: COUNTIF

These formulas use English function names and commas between arguments. Your spreadsheet's language or locale may require localized names or semicolon separators. If a result is wrong, check that seat values imported as numbers and that your formulas include the intended rows.

Also inspect the six seat values directly. They must all be positive whole numbers. A correct grand total can conceal two errors that cancel each other out.

Reconcile both records and seats

The record check is:

8 source records = 6 accepted bookings + 1 confirmed duplicate + 1 unresolved booking.

The seat check is:

AI Basics: 2 + 1 + 2 = 5. Prompt Writing: 3 + 2 + 1 = 6. Total: 11.

If you simply add the numeric entries in the raw sample, treating the word “two” as absent, you get 12. The approved transformation removes the duplicate's 3 seats and adds B005's confirmed 2 seats: 12 − 3 + 2 = 11. This explains the change in the total. It does not resolve B006.

The reconstructed example is checked with executable Node.js assertions: row assignments, approved cell changes, exclusions, unique IDs, positive integer seats, totals and the practice variant. This is a reference calculation, not a live chatbot test. The Google Sheets formulas have been checked against documentation but have not been executed in Google Sheets, Excel or another spreadsheet engine.

5. Write a report that preserves the uncertainty

A suitable summary is:

The reviewed subset contains 6 accepted bookings for 11 seats: 5 for AI Basics and 6 for Prompt Writing. One confirmed duplicate was excluded. Booking B006 remains unresolved and is excluded from these provisional totals. Resolve its seat value before treating this as the complete booking total.

Avoid reporting “There are 11 attendees.” Seats reserved are not attendance, and one booking still needs clarification.

Keep four things together: the raw export, approved cleaning rules, cleaned output and change/exception log. When the source owner resolves B006, update the decision log, rerun the process from the raw data and repeat the checks.

Common problems and what to do

The same ID appears with different values. Ask whether it is a correction, an additional item or a data error. Do not silently keep the first or last row.

Two category names look similar. Confirm whether they mean the same thing. Our exercise allows case and spacing changes; it does not justify merging different courses or products.

A label still does not match after trimming. Google Sheets' TRIM does not remove non-breaking spaces. Inspect the actual characters before broad replacements. Google: TRIM

The assistant says it ran code but shows none. Ask for the execution details and verify the result independently. If code execution is unavailable, use the assistant to explain formulas and run the checks in your spreadsheet.

Your file is much larger than this example. Confirm that the tool processed the entire file. Check source and output record counts, inspect samples from different parts of the file and reconcile every excluded record. Never assume that a preview contains all the data.

Your next practice task

In a copy of the raw sample, change B007's seats from 1 to 4. Rerun the same approved process. You should still have 6 accepted bookings, with 5 AI Basics seats and 9 Prompt Writing seats, for a provisional total of 14. B006 should still appear as unresolved.

If the total changes but the exception disappears, the workflow is losing information. Fix that before applying it to real work.

For a broader review checklist, see Moyan AI's guide to verifying AI output. To continue learning, explore the AI courses.