# Data Cleanup Prompt Pack

Turn a messy spreadsheet into clean, consistent, deduplicated data using any capable AI assistant. ChatGPT, Claude, and Gemini all work. Four prompts run in sequence: profile, normalize, dedupe, validate. A sampling method at the end tells you whether to trust the output.

## What you need

- An AI assistant that accepts file uploads or pasted tables (ChatGPT, Claude, and Gemini all do)
- Your messy data exported as CSV
- An untouched backup copy of the original file
- About 20 minutes for the first run

## Setup

1. Save a backup of the original file before anything else. You need it for verification, and cleanup without a backup is how data disappears.
2. Export the sheet to CSV. Formulas, colors, and merged cells confuse the process. Flat data only.
3. Work in batches. If the file runs past a few hundred rows, split it. Smaller batches keep accuracy up.
4. Open a fresh chat, upload or paste one batch, and run the prompts in order in that same chat.

## Prompt 1 — Profile the mess

```
You are auditing the attached spreadsheet data. Do not change anything yet.

For each column, report:
- What the column appears to contain
- Every distinct format you see (date styles, phone formats, capitalization, abbreviations)
- Blank and placeholder values (empty, "N/A", "-", "TBD", etc.) and how many of each
- Values that look like they landed in the wrong column

Then list rows that look like duplicates or near-duplicates, with row numbers.

Finish with a proposed cleanup plan as a numbered list of rules. Wait for my approval before applying anything.
```

Read the plan. Cut rules you disagree with, add ones it missed, then approve.

## Prompt 2 — Normalize

```
Apply the approved rules to the data, plus these standing rules:

- Standardize formatting only. Never change what a value means.
- Dates: convert to YYYY-MM-DD. If a date is ambiguous (e.g. 03/04/2025), leave it as written and flag it [AMBIGUOUS].
- Trim stray whitespace. Fix broken capitalization ("JOHN smith" -> "John Smith") but leave brand-style names alone ("iPhone", "eBay").
- Collapse obvious variants to one form per column ("CA", "Calif.", "California" -> one form; tell me which you chose).
- Convert every blank and placeholder to a single empty value. Never invent or infer a missing value.
- If you are unsure about any cell, leave it unchanged and flag it [REVIEW].

Return the full cleaned table, then a change log: each rule applied with the count of cells it touched, plus every flagged cell with its row number.
```

## Prompt 3 — Dedupe

```
Find duplicates in the cleaned table. Do not delete anything.

- Exact duplicates: rows identical in every column.
- Near duplicates: rows that likely describe the same real-world thing despite differences — name variants ("Bob" / "Robert"), reordered words, the same email with different name spellings, one row more complete than the other.

Output duplicate groups: group ID, row numbers, why you grouped them, and which single row you would keep (prefer the most complete and most recent). Mark each group EXACT or NEAR. I will decide what to merge or delete.
```

Review NEAR groups yourself. Fuzzy matching is where models overreach.

## Prompt 4 — Validate

```
Run validation checks on the cleaned table and report failures only:

1. Emails: exactly one @ and a plausible domain.
2. Dates: real calendar dates in a plausible range for this data. Flag anything in the future unless the column implies it.
3. Numbers: numeric columns contain only numbers. Flag negatives where they make no sense.
4. Required fields: list rows where [NAME THE COLUMNS THAT MUST NOT BE BLANK] are empty.
5. Cross-field sanity: flag rows where fields contradict each other (city and state that do not match, end date before start date).

Output a failure table: row number, column, value, check failed. Do not fix anything. Flag only.
```

## Verification — the sampling method

Never trust a cleaned file wholesale.

1. Row count check: rows out should equal rows in, minus only the duplicates you chose to remove. If the counts do not reconcile, stop and find out why.
2. Pick a sample the model cannot anticipate: every 10th row, plus five rows you choose yourself, including at least one you know was messy.
3. Compare each sampled row against your backup, field by field. You are hunting two failure modes: values that changed meaning, not just format, and values invented for blanks.
4. If a sampled row fails, do not patch that one row. Add a rule to the relevant prompt, re-run the batch, and re-sample.

## Tips

- Keep a rules list per data source and paste it above Prompt 2 next time. Your CRM export has the same quirks every month.
- If a batch comes back suspiciously perfect, sample harder.

## When this outgrows DIY

This pack is built for one-off cleanups. If the same mess regrows every month, the problem is the intake: forms without validation, systems that do not sync, the same record typed into two tools. Cleaning downstream of that is a treadmill. The fix is validation at the point of entry, continuous deduplication, and pipelines that keep systems in sync automatically.

That is the kind of system agentclaw builds. Start with a free AI opportunity audit at https://agentclawhq.com/book

---

Free resource from agentclaw — AI agents and workflow automation for growing businesses. https://agentclawhq.com