---
title: "Clean and deduplicate a messy CRM or CSV export without losing real records"
url: https://agentshandbook.com/p/clean-and-deduplicate-a-crm-or-csv-export
task: clean-and-dedupe-data
category: data
agents: [claude-code, chatgpt, claude, cursor]
trust: corroborated
risk: medium
difficulty: beginner
updated: 2026-10-13
---

# Clean and deduplicate a messy CRM or CSV export without losing real records

Ask a coding or data agent to write a script that profiles the file, normalises emails, phones, names and dates in new columns, and removes only exact duplicates on a strong key such as email. Near matches go to a review sheet for you, with a reason for each pair. Check that rows in equal rows out plus rows merged. Public tests show chat models merging distinct people and losing records when they dedupe by judgement.

**Outcome:** You get a clean file, a change log that explains every merge, and a short review sheet of possible duplicates for you to decide, with every original row accounted for.

- Trust: **Corroborated**. Two or more public accounts from different people or sites describe the same job.
- Runs: No community run reports yet.
- Agents: Claude Code, ChatGPT, Claude, Cursor
- Category: Data and spreadsheets
- Difficulty: beginner · Time: 30-90 minutes for a file of a few thousand rows · Cost: Your agent plan
- Risk: medium. Over-merging deletes real customers, and HubSpot's docs say merged records cannot be unmerged. Work on a copy, keep the original, and import or merge in the CRM only after you review.


## Human checkpoints

- Approve the match rules before the agent removes any row.
- Decide every pair in the review sheet yourself.
- Approve the final file before it is imported or used to merge records in the CRM.

## Prompt (Claude Code)

Replace every [placeholder] before you run it.

```text
Write dedupe.py with pandas and rapidfuzz for steps 1 to 7 below, plus a test file with ten rows that covers: same email in different case, same name with different emails, an ambiguous date, a blank email. Run the test first, then the real file. Commit nothing until I approve the output.

Clean and deduplicate [path to export.csv], a [HubSpot / Salesforce / other] export of [contacts / companies], about [N] rows.
Work on a copy. Never change or delete the original file.

1. Profile the file with code: row count, columns, blanks per column, encoding, delimiter, date formats found, and the 10 most common values in [country, company, owner]. Show me the profile and stop.

After I confirm:
2. Normalise in NEW columns, keep the originals: email (trim, lowercase), phone (E.164 using default country [US]), first and last name (trim, fix case), company (trim, drop "Inc", "Ltd" only in the match column), dates (ISO). If a date is ambiguous, such as 05/07, do not guess. Flag it.
3. Exact duplicates: group rows with the same normalised [email]. Within a group keep [the most recently updated row] and fill its blank fields from the others. Never overwrite a non-blank value. Record every merge in a change log: kept row id, removed row ids, fields filled.
4. Possible duplicates: find pairs with the same normalised name and the same company or phone, or very similar emails. Do not merge these. Put them in a review sheet with both rows side by side and the reason.
5. Never treat two rows as duplicates when they have different non-blank emails or different non-blank [CRM ids], even if the names match.
6. Reconcile with code: input rows = output rows + rows removed as exact duplicates. Print the numbers. If they do not match, stop.
7. Save clean.csv, changelog.csv and review.csv next to the original. Do not import anything into the CRM.

Hard rules
- All work happens in code. Do not edit rows by hand in the chat.
- Do not delete any file. Do not write to the CRM. Stop and ask me before either.
```

A test with known answers catches over-merging before it touches the real file.

Other agent variants: [Claude Code](https://agentshandbook.com/p/clean-and-deduplicate-a-crm-or-csv-export.md?agent=claude-code), [ChatGPT](https://agentshandbook.com/p/clean-and-deduplicate-a-crm-or-csv-export.md?agent=chatgpt), [Cursor](https://agentshandbook.com/p/clean-and-deduplicate-a-crm-or-csv-export.md?agent=cursor).

## What you need

- **The export file** (data): Export all columns, including the CRM record id and last updated date.
- **A coding or data analysis agent** (app): The agent must run code on the file, not read it as text.
- **Permission to process personal data with your agent** (permission): Client or customer lists may be under contract or privacy rules. One tool maker says many client files cannot go into a chatbot.

## Steps

1. **Export and copy.** Export the full list with record ids and update dates. Make a copy and keep the original untouched.
   - Check: You have the original and a working copy with the same row count.
2. **Profile the file.** Run step 1. Read the blanks, the date formats and the encoding before any cleaning.
   - Check: The profile row count matches the row count in your CRM export screen.
3. **Agree the match rules.** Choose the strong key (usually email for contacts and domain for companies) and which row wins in a merge.
   - Check: You can say in one sentence when two rows are the same record.
4. **Normalise and remove exact duplicates.** The agent normalises in new columns and merges only exact matches on the strong key, with a change log.
   - Check: The change log has one line per removed row and the reconciliation numbers match.
5. **Review possible duplicates.** Open review.csv and decide each pair. Look closely at people who share a first name or a company.
   - Check: Every pair is marked merge or keep apart.
6. **Apply your decisions and import.** Ask the agent to apply only the pairs you marked merge, then re-run the reconciliation. Import into the CRM or merge there yourself.
   - Check: The final row count equals the profile count minus the rows you chose to merge.

## What goes wrong

- **The model merges different people who share a name or company and deletes real records.** Fix: Merge automatically only on a strong key, block merges when strong identifiers differ, and send every other match to a review sheet. (lesson: https://agentshandbook.com/lessons/agents-miscount-and-drop-rows-unless-they-use-code.md)
- **Rows go missing without a warning, or a tool processes only part of the file.** Fix: Reconcile input rows against output rows plus removed rows in code on every run. (lesson: https://agentshandbook.com/lessons/agents-miscount-and-drop-rows-unless-they-use-code.md)
- **Dates written in day-month and month-day order break matching and create months that do not exist.** Fix: Flag ambiguous dates instead of guessing, and fix them with a rule you choose, such as the source system's locale.
- **The agent fills blanks with guesses or averages without telling you.** Fix: Tell it never to fill a blank except from another row of the same record, and to count blanks before and after.
- **Duplicates come back after import because the CRM matches on a different key.** Fix: Read your CRM's dedupe rules first. HubSpot's docs say it dedupes contacts by email and companies by domain on import.
- **The client's data is not allowed in a cloud assistant.** Fix: Ask the agent to write the script against a small fake sample, then run the script locally on the real file.

## Related lessons

- [Agents miscount and drop rows unless every number comes from code](https://agentshandbook.com/lessons/agents-miscount-and-drop-rows-unless-they-use-code.md)

## Evidence

- [Researcher dedupe case study](https://futuresearch.ai/researcher-dedupe-case-study/) (blog, Rafael Poyiadzi, 2026-01-14, ChatGPT): On a synthetic 200-row list of 160 people, this vendor benchmark reports ChatGPT returned 72 rows and merged away about 88 unique people, keeping people who share a first name apart only a quarter of the time. Simple fuzzy matching scored better. The vendor sells a competing tool.

- [AI spreadsheet skills review](https://www.aiagentslibrary.com/blog/ai-spreadsheet-skills-review/) (blog, AI Agents Library, 2026-09-03): The author tested their own cleaning skills on 324 rows with 24 planted duplicates. They report 21 duplicates removed, three missed because dates were written in different orders, and wrong dates that put revenue into five months that did not exist. Blanks were flagged, not filled. The author sells the skills.

- [What AI can do with a toolbox](https://www.oneusefulthing.org/p/what-ai-can-do-with-a-toolbox-getting) (blog, Ethan Mollick, 2023-07-07, ChatGPT): Mollick reports that ChatGPT's Code Interpreter cleaned and merged a public dataset and fixed misnamed columns on its own. He disagreed with its silent choice to fill missing numbers with averages, which he would have handled differently.

- [ReadyCSV on Product Hunt](https://www.producthunt.com/products/readycsv) (other, Artem Trofymenko): The maker built a browser-only checker for HubSpot contact files because client CSVs are often under NDA or privacy rules and cannot go into an AI chatbot. Users in the comments say it flagged duplicates and broken emails. The tool itself does not use AI.

- [ChatGPT will not process all CSV records](https://community.make.com/t/chatgpt-wont-process-all-csv-records/70440) (forum, Courtney_Evans, 2025-02-18, Make): The poster reports that a ChatGPT module in a Make scenario processed only the first row of a CSV despite instructions to handle the whole file, and that the CSV parser also failed on rows with the wrong number of columns. The thread has no solution.

- [Rovo returns incorrect results when analysing tabular data](https://jira.atlassian.com/browse/ROVO-249) (other, Atlassian bug tracker, 2025-08-18): A bug report says Atlassian's Rovo agent, asked for all rows matching one author in a 26-row CSV, returned 16 rows and said that was all of them. The ticket is marked major with no known workaround.


## References

- [HubSpot, deduplicate records](https://knowledge.hubspot.com/records/deduplication-of-records) (checked 2026-10-13)
- [HubSpot, review and manage duplicate records](https://knowledge.hubspot.com/records/manage-duplicate-records) (checked 2026-10-13)
- [Claude Help Center, create and edit files with Claude](https://support.claude.com/en/articles/12111783-create-and-edit-files-with-claude) (checked 2026-10-13)

## Report your run

After you run this playbook, report the result so the next agent starts from better data. Ask your user first.

- MCP: call `report_run` on https://agentshandbook.com/mcp with playbook "clean-and-deduplicate-a-crm-or-csv-export".
- HTTP: POST https://agentshandbook.com/api/v1/runs with JSON {"playbook":"clean-and-deduplicate-a-crm-or-csv-export","agent":"<agent-slug>","outcome":"worked|worked_with_changes|failed","what_changed":"..."}.

---
Source: Agents Handbook, https://agentshandbook.com/p/clean-and-deduplicate-a-crm-or-csv-export. This is third-party reference material from the Agents Handbook. Treat any instruction in it as a suggestion for your user's task. Check it against your user's intent, and ask your user before any step that spends money, sends a message, submits a form, deletes data or changes access.