AI-Assisted Data Cleaning

What you'll build

By the end of this tutorial you'll have a repeatable AI-assisted workflow for cleaning a messy CSV file (customer records, event logs, survey responses, sales exports — whichever your case is). The workflow: profile the dataset to expose actual issues, transform explicitly on a copy, validate against source, then accept. No overwrites, no invented values, full diff.

We'll use a fabricated customer-export dataset with the standard 2026 mess — inconsistent date formats, mixed-case country names, whitespace-padded emails, near-duplicate rows from CRM merges, missing values in fields that should have them, and a couple of outliers that look like data-entry errors. If your real data is messier, the workflow still applies; if it's cleaner, some steps will be quick.

Prerequisites

• An AI provider with Skills support (Anthropic Claude Desktop, OpenAI GPTs, Google Gemini Gems, or equivalent).
• The CSV Data Cleaning and Normalization Skill installed. We'll set this up in step 2 if you don't have it.
• A messy CSV file to work on. If you don't have one handy, the tutorial download includes a fabricated customers-messy.csv you can use.
• Basic comfort with the concept that AI outputs need validation — not blind trust and not blanket rejection.
• Optional: a spreadsheet MCP (Google Sheets or Excel) if your source is a sheet rather than a file. Everything else in the tutorial is CSV-based.

01Profile the mess before touching it

The most common mistake in AI-assisted cleaning is starting with "clean this file." That skips the step where you find out what's actually wrong — and if you skip it, the AI cleans what it recognizes as messy and leaves whatever it doesn't recognize untouched. Start with profiling instead.

Give the AI the CSV (or first 500 rows) and ask specifically:

Profile this dataset. For each column, tell me:
  - the data type it appears to be
  - the format(s) actually used (if inconsistent, list all)
  - the distribution of value lengths
  - suspected quality issues (missing, malformed, potential
    duplicates across rows, outliers, encoding issues)
  - a sample of 3 clearly-bad values if any

Do not fix anything yet. Do not make assumptions about
what values should be. Report only what is present.

The last two sentences matter more than they look. Without them, most models will preemptively describe what they'd change rather than what's actually there. You want a diagnosis, not a treatment plan.

What you should get back is something like:

PROFILE REPORT — customers-messy.csv (487 rows, 8 cols)

col: signup_date
  types seen: date-like
  formats: 2026-08-15 (312 rows), 8/15/2026 (94),
           15-Aug-2026 (61), 20260815 (18), blank (2)
  issues: 4 distinct date formats + 2 blanks
  bad samples: "08152026", "August fifteen 2026"

col: email
  types seen: string
  lengths: min 8, max 89, mean 24
  issues: whitespace padding (114 rows), mixed case (all),
    3 rows with trailing semicolons, 8 with no @ symbol
  bad samples: "jane.doe;", "   priya@co.com   ", "bob at co"

col: country
  types seen: string
  distinct values: 47 (expected ~15-25 based on customer base)
  issues: mixed case + duplicated concepts
  bad samples: "USA", "U.S.A.", "United States", "us", "U.S"

col: revenue_usd
  types seen: number-like
  issues: 3 rows with values >100x median (possible errors),
    12 rows with $ prefix, 8 with comma thousands separators,
    1 row negative (refund? or error?)
  bad samples: "$1,247.00", "-84", "947239847" (vs median 320)

[... other cols ...]

02Set up the CSV Cleaning Skill

You could do the whole tutorial without a Skill, but the Skill enforces the rules you'd otherwise have to re-specify every time — never overwrite source, never invent values, always emit a diff report. Install once, use for every future dataset.

# Download the Skill folder from skillsandservers.com/skills/
#   csv-data-cleaning-and-normalization/

# Claude Desktop:
unzip csv-data-cleaning-and-normalization.zip \
  -d ~/skills/

# OpenAI GPTs:
# Upload the SKILL.md as knowledge to your Custom GPT.

# Gemini Gems:
# Paste SKILL.md contents into Gem instructions.

Verify the install by asking "Load the CSV data cleaning Skill and describe what you'll and won't do." A correct load returns a short summary that mentions: profile-before-clean workflow, never-overwrite-source rule, never-invent-values rule, and diff-report output as default.

For everything after this step, prefix your requests with "Using the CSV cleaning Skill," so the AI keeps those rules loaded.

03Standardize dates and text formats

Now do the easy wins. Date formats and text normalization are where AI-assisted cleaning shines relative to hand-written scripts — you don't have to enumerate every format variant; the AI infers them from the profile output.

Using the CSV cleaning Skill, apply these transformations
to customers-messy.csv (save to customers-clean-v1.csv,
do NOT overwrite source):

1. signup_date: standardize all 4 formats to ISO 8601
   (YYYY-MM-DD). For the 2 blank rows, keep blank —
   do not infer.

2. email: strip leading/trailing whitespace, lowercase,
   remove trailing semicolons. Flag rows without @ in a
   separate email_flags column for manual review —
   do NOT delete or invent them.

3. name: title-case the value UNLESS it's already all-caps
   with 3+ letters (that's an acronym-y name or shouty
   entry — flag for review, don't auto-transform).

Emit a diff report at the end: rows changed, columns changed,
count of flags raised, sample of 10 before/after pairs.

The response should be a written summary of what it did, plus the artifact file, plus a diff. You want to see something like:

APPLIED TRANSFORMATIONS — customers-clean-v1.csv (487 rows)

signup_date:
  • 173 rows converted to ISO 8601 (from 3 non-ISO formats)
  • 2 blank rows preserved as blank
  • 0 rows flagged (all parseable)

email:
  • 114 rows stripped of whitespace
  • 487 rows lowercased (all)
  • 3 rows had trailing semicolons removed
  • 8 rows flagged for manual review (no @ symbol)
    • flags visible in new email_flags column

name:
  • 421 rows title-cased
  • 12 rows preserved as all-caps and flagged
    (e.g. "MOHAMMAD ALI" — likely correct, needs
    human confirmation)

DIFF SAMPLE (before → after):
  row 003: signup_date "8/15/2026" → "2026-08-15"
  row 017: email "   Jane.Doe@Co.com   " → "jane.doe@co.com"
  row 034: name "jane DOE" → "Jane Doe"
  [...7 more samples...]

FLAGS RAISED: 20 (8 email + 12 name). See flags columns.

04De-duplicate with fuzzy matching

Real duplicates rarely look identical. Two CRM systems merged and now "Jane Doe" at "jane.doe@acme.com" is one row, and "J. Doe" at "jane.doe@acme.com" is another. Exact-match de-dup misses this. Fuzzy matching handles it, but you need to control the threshold or the AI will start collapsing rows that shouldn't merge.

Using the CSV cleaning Skill, on customers-clean-v1.csv:

Identify candidate duplicates using these rules:
  • email match (exact, post-normalization): high-confidence dupe
  • name similarity >= 0.9 AND same email domain: probable dupe
  • name similarity >= 0.85 AND same country + signup within
    30 days: possible dupe

For each candidate cluster:
  • do NOT merge yet
  • output cluster ID, confidence level, and the row IDs
  • propose a "keeper" row (most complete data) and note what
    would be lost if others were dropped

Save to customers-dupes-review.csv for manual approval.

The critical word here is review. Auto-merging duplicates without human review is where the most damaging cleanup mistakes happen — and they're usually silent, discovered weeks later when someone notices a record vanished. The Skill's default behavior is to output candidate clusters for approval rather than merge automatically. Leave that default on.

DUPLICATE CANDIDATES — 23 clusters identified

CLUSTER 1 (high-confidence, email match)
  Row 042: Jane Doe, jane.doe@acme.com, USA, signup 2026-03-14
  Row 419: J. Doe, jane.doe@acme.com, USA, signup 2026-08-01
  Proposed keeper: Row 042 (more complete: has phone, industry)
  Data lost if merged: signup_date 2026-08-01 (later of the two)

CLUSTER 2 (probable, name+domain)
  Row 088: Marcus Chen, marcus.c@fintech.io, Singapore
  Row 297: Marcus Chen, m.chen@fintech.io, Singapore
  Proposed keeper: Row 088 (has full first name in email)
  Data lost: alternate email m.chen@fintech.io

CLUSTER 3 (possible, name+country+time)
  Row 121: Priya Shah, priya@vendorco.com, India, signup 2026-06-02
  Row 138: Priya Shah, priya.s@personal.com, India, signup 2026-06-19
  RECOMMEND: DO NOT MERGE — different email domains suggest
    possibly work+personal accounts of same person, keep both
    or flag for the account owner to decide.

[...20 more clusters...]

SUMMARY: 23 clusters, ~35 rows affected. Review before applying.

05Normalize categorical values

The country column had 47 distinct values for what should be ~20. This is the classic categorical-normalization problem — and it's where the AI is most likely to get creative in unhelpful ways. The rule: normalize against a controlled vocabulary you provide, not against the AI's memory of country lists.

Using the CSV cleaning Skill, on customers-clean-v1.csv:

Normalize the 'country' column against this controlled
vocabulary (ISO 3166-1 alpha-2 codes):

  US, GB, CA, DE, FR, IN, SG, JP, AU, MX, BR, ZA, KE,
  NG, AE, SA, EG, TR, IT, ES, NL, SE, PL, IE, PT, PK,
  BD, ID, VN, TH, PH, KR, CN, HK, TW

For each raw value, map to the corresponding code. If the raw
value doesn't map cleanly (ambiguous, non-country, typo you
can't confidently resolve), leave the original value and add
a country_flag column with the reason.

Output the mapping table you used (before/after) so I can
verify. Do NOT skip that.

You want the mapping table because that's the audit trail. Without it, a wrong mapping ("Georgia" the country vs "Georgia" the US state, for example) is invisible in the output CSV but obvious in the mapping table.

COUNTRY NORMALIZATION MAPPING TABLE

  "USA" → US (34 rows)
  "U.S.A." → US (8)
  "United States" → US (27)
  "us" → US (12)
  "U.S" → US (3)
  "United Kingdom" → GB (19)
  "UK" → GB (14)
  "U.K." → GB (2)
  "India" → IN (41)
  "IN" → IN (6)   [already-code, kept]
  "Singapore" → SG (18)
  "S'pore" → SG (2)
  ...

FLAGGED (not mapped, needs human review):
  "Georgia" (3 rows): ambiguous — country GE or US state?
    Flag reason: cannot resolve without additional context.
  "Korea" (5 rows): ambiguous — KR (South) or KP (North)?
    Flag reason: default vocabulary uses KR but source
    ambiguity should be human-verified.
  "EMEA" (2 rows): region, not country.
    Flag reason: no country code represents a region.

RESULT: 44 raw values → 22 country codes + 3 flag categories.

06Handle missing values and outliers

Missing values and outliers are the two places where AI-assisted cleaning most often introduces subtle bugs. The temptation is to impute missing values (fill blanks with something) and to correct outliers (edit implausible values to plausible ones). Both are wrong by default. The right defaults: preserve missing values as missing, and flag outliers rather than correct them.

Using the CSV cleaning Skill, on customers-clean-v1.csv:

For missing values:
  • Do NOT impute or infer.
  • For each column with missing values, report count and
    what percentage of rows are affected.
  • Suggest whether the missingness looks structural
    (this field is optional or new) or accidental (data
    entry gap) — but do NOT act on the suggestion.

For outliers in revenue_usd:
  • Compute median and IQR.
  • Flag rows >5x median as "possible outlier" in an
    outlier_flag column.
  • Do NOT change the values.
  • Suggest a small number (3-5) most likely to be
    data entry errors based on shape, but do NOT act.

Output a summary report.

MISSING VALUES REPORT

  signup_date: 2 rows missing (0.4%). Structural: appears
    accidental — suggest revisiting original CRM records.
  phone: 89 rows missing (18.3%). Structural: phone appears
    optional in signup flow; missingness likely intentional.
  industry: 12 rows missing (2.5%). Ambiguous — column
    populated for most; sparse cluster suggests newer records
    where field was added later.
  revenue_usd: 4 rows missing (0.8%). Structural: likely
    non-paying users; do not impute.

OUTLIERS — revenue_usd (median $320, IQR $180-$540)

  Flagged as >5x median (14 rows):
    Row 049: $1,200 (rich customer? Or entry error?)
    Row 172: $947,239.87 (very likely entry error — extra digits)
    Row 203: $2,400 (rich customer plausible)
    [...11 more...]

  Most-likely-data-error suggestions (3):
    Row 172: $947,239.87 — two extra zeros suspected
    Row 388: $-84 — negative revenue, refund vs error unclear
    Row 411: $0.00 in a paid-customer segment — check invoice

No values changed. See outlier_flag column for full set.

07Validate before overwriting

You now have customers-clean-v1.csv plus a de-dup review file, a country mapping table, a missing-values report, and an outlier report. Before this replaces your source, run a validation pass.

Using the CSV cleaning Skill, validate customers-clean-v1.csv
against the source customers-messy.csv:

1. Row count check: cleaned should equal source (487 rows).
   Report any mismatch immediately — that's a bug.

2. Preservation check: for 20 randomly-sampled rows,
   confirm that no factually-different values exist between
   source and cleaned that weren't part of an explicit
   transformation. Flag any suspicious drift.

3. Flag summary: total flags raised across all transformations,
   grouped by column and reason.

4. Emit a final "acceptance" report the reader can approve
   before renaming customers-clean-v1.csv to customers.csv.

VALIDATION REPORT — customers-clean-v1.csv vs customers-messy.csv

ROW COUNT: 487 (source) = 487 (cleaned). PASS.

PRESERVATION CHECK (20 random rows):
  All checked rows had transformations traceable to the diff.
  No unexpected value drift detected.
  Rows checked: 003, 017, 034, 088, 121, 172, 203, 297, 388,
    411, 425, [10 more].

FLAG SUMMARY (unresolved, need human review):
  email_flags: 8 rows (no @ symbol)
  name_flags: 12 rows (all-caps, preserved)
  country_flags: 10 rows (ambiguous mappings)
  outlier_flags: 14 rows (>5x median revenue)
  Total: 44 rows need human review before dataset is fully clean.

DUPLICATE CLUSTERS: 23 not yet merged — separate review needed.

ACCEPTANCE RECOMMENDATION:
  ✓ Safe to rename to customers.csv IF you accept the 44 flags
     as work-to-do rather than blockers.
  ✓ De-dup should be resolved BEFORE renaming to avoid
     shipping known-duplicate rows.
  ✓ Keep customers-messy.csv archived for rollback (do NOT
     delete for at least 30 days).

At this point you have three artifacts: the cleaned CSV, the flag review file (44 rows), and the dedup review file (23 clusters). None of them replace your source until you've worked through the review files. That's the discipline the workflow enforces — and it's the difference between AI-assisted data cleaning that you can defend in an audit and AI-assisted data cleaning that quietly corrupts your dataset over six months.

Common pitfalls

• Skipping the profile step. Every dataset has a category of mess you didn't anticipate. Profiling surfaces it before the AI can quietly mishandle it.
• Auto-merging duplicates without review. The most damaging cleanup mistakes are silent record deletions. Always review cluster candidates before applying merges.
• Imputing missing values by default. Missing means missing. Filling with means, medians, or "unknown" is a downstream analysis decision, not a data cleaning decision.
• Auto-correcting outliers. The obvious data-entry error is sometimes a real enterprise account. Flag and let the account owner decide.
• Skipping the diff. If you can't point at a diff and say "these are the changes," you can't defend the cleanup when someone asks what happened.
• Overwriting source. Never. Keep the messy source for 30+ days minimum. Rollback capability is worth the disk space.

Where to go next

The Skill that automates this workflow

Everything above is enforced by the CSV Data Cleaning and Normalization Skill so you don't have to re-specify the rules every time.

Safe database access for cleaning larger datasets

If your data lives in a database rather than a CSV, pair this workflow with the Safe Read-Only MCP Databases tutorial to profile and clean without risking production writes.

Building validated workbooks in Excel

If you're cleaning data inside a live spreadsheet rather than a CSV file, the Building Bulletproof Excel Workbooks tutorial covers layered validation for Excel-native workflows.

Frequently asked questions