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.
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.
- Difficulty
- Beginner
- Time
- 30-90 minutes for a file of a few thousand rows
- Sources
- 6 · 6 independent
- Updated
- 2026-10-13
- Cost:
- Your agent plan
The prompt
Fill the blanks here, or leave them for your agent to ask. Pick the tab for your agent. The hand-off button gives any agent this page as markdown.
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.
Human checkpoints: the agent stops and asks you
- 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.
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.
What you need
- The export filedata
Export all columns, including the CRM record id and last updated date.
- A coding or data analysis agentapp
The agent must run code on the file, not read it as text.
- Permission to process personal data with your agentpermission
Client or customer lists may be under contract or privacy rules. One tool maker says many client files cannot go into a chatbot.
Steps
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.
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.
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.
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.
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.
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.
Read the lesson →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.
Read the lesson →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.
Evidence
Two or more public accounts from different people or sites describe the same job. We summarise each source in our own words. Open the original before you rely on it.
- Blog2026-01-14· ChatGPTResearcher dedupe case study
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.
Rafael Poyiadzi · futuresearch.ai - Blog2026-09-03AI spreadsheet skills review
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.
AI Agents Library · aiagentslibrary.com - Blog2023-07-07· ChatGPTWhat AI can do with a toolbox
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.
Ethan Mollick · oneusefulthing.org - WebReadyCSV on Product Hunt
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.
Artem Trofymenko · producthunt.com - Forum2025-02-18· MakeChatGPT will not process all CSV records
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.
Courtney_Evans · community.make.com - Web2025-08-18Rovo returns incorrect results when analysing tabular data
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.
Atlassian bug tracker · jira.atlassian.com
- HubSpot, deduplicate recordschecked 2026-10-13
- HubSpot, review and manage duplicate recordschecked 2026-10-13
- Claude Help Center, create and edit files with Claudechecked 2026-10-13
Report how it went
Every report makes this page better for the next person and the next agent. Reports are reviewed before they count. Agents can report with the report_run tool.
Loading reports…