Deduplicating data before you import it: exact, fuzzy and key-based

“Anna Berg” and “anna berg ” are the same person and different strings. Before any duplicate check, fold the differences that carry no meaning: trim the edges, collapse runs of spaces, replace non-breaking spaces with ordinary ones, remove zero-width characters, and lower-case for comparison purposes. Do the folding in a comparison key, not in the data, so the row you keep still shows the original spelling.

Unicode adds a subtler trap: «é» can be one code point (U+00E9) or two (e + U+0301), and the two look identical on screen while comparing as different strings. Normalising to NFC — the composed form, which is what String.prototype.normalize(“NFC”) produces in every browser — collapses them. It matters whenever your data has passed through macOS file names or older web forms.

Field-specific normalisation is the next layer: lower-case the domain part of an email, strip spaces and dashes from phone numbers, drop a leading + and a country code you know is constant. These rules are yours to choose, and they are exactly where a generic “remove duplicates” button stops helping.

An exact duplicate is a row whose every cell matches an earlier row byte for byte. Removing those is almost always safe, because the file cannot be telling you anything by repeating itself perfectly — unless the row is a legitimate event, like two identical payments of the same amount on the same day. Check whether your table has a natural unique column before you assume repetition is noise.

The “ignoring case and whitespace” variant catches the rows that exact matching misses: the same record re-entered by a different operator, or merged from a system with different capitalisation rules. It removes more rows than the exact pass, which is why it is offered separately and why previewing the removals before applying them is worth the extra click.

In practice the question is not “are these rows identical” but “do these rows describe the same thing”. That is answered by a key: an email address, an order number, a tax ID, or a combination such as name + postcode. Two rows with the same key are the same entity even if every other column differs — and those differing columns are usually the point, because one of them is more recent.

So a key-based pass forces a second decision: keep the first occurrence or the last? Keep the first when the file is ordered oldest-to-newest and the earliest record is the authoritative one; keep the last when later rows are updates. If the file has a timestamp column, sort by it before deduplicating so that “last” means what you think it means.

A practical way to do this without a database: sort by the key column, then look at the neighbours. Rows that describe the same entity land next to each other, and a quick scan tells you whether the differences are updates, typos, or genuinely different records that happen to share an email — a shared family address, for instance.

Fuzzy matching finds pairs that are close but not equal — “Jon Smith” and “John Smith”, “Acme Ltd” and “Acme Limited”. The usual measure is edit distance (how many single-character changes turn one string into the other) or a token overlap score. It is the right tool for merging two company lists or cleaning a form where people typed their own names.

It is the wrong tool for identifiers. Two order numbers differing by one character are two different orders, and an edit-distance rule will merge them silently. Restrict fuzzy matching to human-written text columns, always require a second signal (same postcode, same domain), and always review the proposed pairs by hand — the false positives are the expensive ones.

CSV Polish deliberately stops before fuzzy matching. It normalises aggressively — case, whitespace, invisible characters, formats — which turns a large share of “nearly equal” rows into exactly equal ones that the loose duplicate pass then removes. What remains genuinely needs a human or a dedicated matching tool with a review queue.

CRM imports are the unforgiving case. Most systems match incoming rows against existing records on one field — usually email — and create a new contact when it does not match. Duplicates therefore multiply across every subsequent sync, campaign statistics split between twins, and deduplication after the fact means merging records that already carry separate activity histories.

Email platforms bill per contact and count a duplicate as a subscriber, so the cost is immediate and recurring. Worse, the same person receives the campaign twice, which drives unsubscribes and, in some jurisdictions, complaints. A five-minute pass over a CSV is cheaper than either.

Keep the evidence. Note how many rows the file had, what you normalised, which duplicate definition you used and how many rows each pass removed; CSV Polish keeps that list for you and lets you copy it. When somebody asks in three weeks why the import has 1,847 rows and not 2,013, the answer is in one paragraph instead of an afternoon.