Why importers reject files
Before touching duplicates, rule out the structural faults, because none of them are about your data being wrong and all of them will stop the import dead.
- Inconsistent column counts. One unquoted comma inside a value splits that row into an extra field, and most importers refuse the whole file rather than guess. Opening the CSV in a proper CSV-aware tool rather than a text editor makes these obvious immediately, because the offending row is the one with a stray column.
- Encoding. A file saved as plain ANSI and read as UTF-8 turns every accented character into nonsense. Save as CSV UTF-8 from Excel. Some importers also choke on the byte order mark Excel writes at the start of that format, which is worth knowing if the very first column header is the one it says it cannot find.
- Header names. Importers map columns by header text. Extra spaces, a renamed column, or two columns with the same name will break the mapping even though the data is fine.
- Required fields. A blank in a field the target system marks as mandatory fails that row, and often the batch it lives in. Filter for blanks in those columns first.
- Size limits. Most import tools cap the file or the row count. Splitting a large export into batches is easier before you have started than after a partial failure.
Pick the key column before you dedupe anything
Deduping is only as good as your definition of "the same record", and that decision is the whole job. For people, use email. It is the closest thing a contact record has to an identity, it is what most CRMs and mailing platforms match on internally, and it is exact, so there is no judgement call. Normalise it first: lowercase and trim, since Bob@Example.com and bob@example.com are the same mailbox but not the same string. The email list guide covers the awkward cases, including plus-addressing and Gmail dots.
Do not use name as the key on its own. Common names collide, and a dedupe that silently deletes a real customer because they share a name with another one is worse than the duplicates you started with. Where there is no email, a composite key is safer: name plus company, or name plus postcode. In the CSV duplicate remover that is just clicking two chips instead of one.
Then deal with the near-duplicates
Exact matching on email catches the honest repeats. What it leaves is the same person entered twice with two different addresses, a work one and a personal one, or a typo in one of them. For those, run a second pass on the name column with fuzzy matching on, starting the similarity threshold at 0.88. Then look at the removed count and be suspicious of it. A number far larger than you expected means the threshold is too loose for this data, not that you had more duplicates than you thought.
One limit worth stating plainly, because it gets oversold elsewhere: fuzzy matching compares how similar two strings are, so it catches reordered names, formatting differences and typos. It will not pair Bob Miller with Robert Miller, because those are not similar strings. Nothing based on similarity will.
The pre-import checklist
- Keep the original. Copy the export somewhere before you edit it. It is your only rollback if the import goes wrong.
- Fix structure first: encoding, column counts, header names, required fields.
- Normalise the key column: trim whitespace and lowercase the email.
- Dedupe on that key and read the kept and removed counts before downloading. Nothing is uploaded, so this costs you nothing but a look.
- Run a fuzzy pass on names if the list was typed by hand, and review the count rather than trusting it.
- Import a small test batch first, twenty rows or so, and check how they landed before committing the rest.
- Use the importer's own update-existing option so the import matches on your key instead of creating fresh records.
Stop the next one at the source
A file that needed cleaning usually came from somewhere that will produce another one. If the export is from a CRM, the duplicates are already in the CRM, and the CRM contact dedupe workflow is the fix for that rather than cleaning each export forever. If the rows arrive from a form or an automation, adding a lookup step before the create step is a smaller change than any amount of downstream cleaning. And for contact files specifically, the contact dedupe tool opens with fuzzy matching already enabled, which saves a click on the pass you will be running anyway.
FAQ
Frequently asked questions
Which column should I dedupe a contact CSV on?
Email, in almost every case. It is the closest thing to a real identity in a contact record, it is what most import tools match on themselves, and it is exact so there is nothing to second-guess. Names are a poor key: two different people share one often enough to lose real records.
Why does my import tool reject the file?
The usual causes are structural rather than duplicate-related: a column count that changes partway down because an unquoted comma split a field, the wrong character encoding turning accents into mojibake, a header row whose names do not match what the importer expects, or a required field left blank on some rows. Fix those before worrying about duplicates.
Should I dedupe before or after importing?
Before. An import that creates duplicate records is far more work to undo than a file that takes two extra minutes to clean, because once the records exist they collect activity, notes and IDs of their own. Cleaning the file also means the import report tells you about real problems instead of noise.
What do I do about near-duplicates that are not exact matches?
Run a second pass on the name column with the API's fuzzy matching, review the removed count, and treat anything that looks high as a signal to raise the threshold rather than accept it. What fuzzy matching will not do is pair a nickname with a given name, because Bob and Robert are not similar strings. Those you catch on email or by hand.
Does deduplicating save me money?
Often, yes, in a boring way: most CRMs and email platforms bill on records or contacts stored, so a duplicate is a record you pay for and gain nothing from. The larger cost is usually operational, since two records for one customer means split history and two people being contacted about the same thing.
Last updated