Removing duplicates from a CSV without losing real data

Deduplicating a CSV sounds mechanical: find identical rows, delete the extras. The mechanical part is easy — one streaming pass even on a million rows. The hard part is a decision hiding inside the word “identical”: what makes two rows the same record? Answer it wrong in one direction and duplicates survive; wrong in the other direction and you silently delete real data. This guide is the decision, made carefully.

Start with why the duplicates exist

The origin of the duplicates tells you what kind of key will catch them:

The same export ran twice (or two exports were concatenated): rows are byte-identical. Whole-row dedupe catches all of it, risk-free.
The same entity entered twice (double form submission, re-import after edits, two source systems): rows share identity fields — email, order ID, SKU — but differ in timestamps, formatting or freshness. Only a key-column dedupe catches these.
Formatting drift: ada@example.com vs Ada@Example.com — same record wearing different whitespace and case. Any key works, but only if comparison normalizes first.

If you don’t know which situation you’re in, profile before you delete: column stats show distinct counts per column — an email column with 40,000 rows and 38,500 distinct values announces ~1,500 candidate duplicates before you’ve removed anything.

Choosing the key

Whole row (all columns). The conservative default. It can never merge two genuinely different records, because any differing field keeps both rows. Its blind spot is exactly that strength: the same customer with an updated phone number survives as two rows. Use it for “the export doubled” situations, and as the safe first pass in any cleanup.

One identity column (email, order ID, national ID, SKU). The right key when the column genuinely identifies the record — but understand what removal does: the entire later row is dropped, including fields where the later row was fresher. Dedupe-by-key is a keep-one-version operation, not a merge. If you need field-level merging (keep the newest non-empty value per field), that’s beyond dedupe — do it in a database or script.

A combination (name + ZIP, first+last+company, address+unit). For data without a real ID. Be deliberately suspicious here: name+city considers two John Smiths in Boston one person. Add enough columns that a collision requires an actual coincidence you can accept — and know your data’s edge cases (households share addresses; branches share company names).

The traps that make “identical” lie

Whitespace. Trailing spaces are invisible and everywhere — "Ada " ≠ "Ada" to a byte-comparison, so the duplicate survives and you conclude there weren’t any. Trim-insensitive comparison (or a trim pass first) is the cure; it’s why the dedupe tool here ignores surrounding whitespace by default.

Case. Emails are case-insensitive in practice; product codes usually too; people’s names are case-sensitive signal (“LaTonya”). Default to ignoring case for identifier columns and think twice elsewhere.

Look-alike formatting. 02134 vs 2134 (a ZIP that lost its leading zero in Excel), +1 (617) 555-0100 vs 6175550100, 2024-03-31 vs 3/31/24 — same fact, different strings, and no generic tool should guess they match. Normalize these columns first (find & replace with regex does phones and dates), then dedupe.

Empty keys. Rows where the key column is blank all share the “same” key. Deduping 40,000 rows on email when 500 rows have no email quietly deletes 499 unrelated records. Filter key-is-empty rows aside first, dedupe the rest, decide the blanks separately.

Which copy survives

Keep-first is the standard rule (it’s what this site and Excel both do): the first row with each key, in current order, wins. That makes sort order part of your dedupe. Want the most recent record per customer? Sort by your date column descending first, then dedupe — newest now comes first, so newest is kept. Want the original? Sort ascending. Unsorted concatenated exports default to “whichever file came first”, which is at least honest but worth choosing consciously.

A method that doesn’t lose data

Count first. Note total rows and the key column’s distinct count — that’s your expected result size (row counter, or the status bar).
Look at actual duplicates. Filter to one suspicious key value and read the copies. Ten seconds of looking beats any amount of theory about your data.
Normalize what formatting hides: trim, unify case on identifier columns, fix known look-alike formats.
Dedupe with the chosen key — after sorting, if survivor-choice matters.
Verify: rows removed should equal (total − distinct keys) from step 1. A mismatch means your mental model of the data was wrong — investigate before exporting.
Export a copy. Never overwrite the original; it’s the undo for every mistake above.

The removed-count check in step 5 is the one professionals lean on: it converts “I think it worked” into arithmetic. When the numbers agree, you can hand the file onward with a straight face — and when they don’t, you just caught a data problem while it was still free to fix.