How to remove duplicate rows from a CSV file

For exact duplicate lines, with the header preserved and the original order kept:

awk 'NR==1 || !seen[$0]++' input.csv > deduped.csv

That handles the easy case, and the easy case is rarer than it looks. Most "duplicates" in a working file are two records for the same person with a different capitalisation, a trailing space, or an updated phone number, rather than identical lines. Deleting the wrong one of those loses data, so the order of operations matters: decide what makes two rows the same, then look at what differs between them, and only then delete.

Step one: define the key

A duplicate is two rows that describe the same thing. Which columns establish that is a decision about your data, not a property of the file.

• A whole-row duplicate, every field identical, is almost always a mistake: a double import, a re-run job, an append that ran twice. Safe to remove.
• A key duplicate, same email or same order_id but different everything else, is a data question. One of those rows is probably newer, or more complete, and you have to choose.

Getting this backwards is the standard way to lose records. Deduplicating on name in a customer list will merrily delete two different people called James Smith.

Step two: count before you delete

Never run a dedupe blind. Look at what you are about to remove:

duckdb -c "SELECT email, count(*) n FROM read_csv('input.csv') GROUP BY email HAVING n > 1 ORDER BY n DESC LIMIT 20"

Two numbers tell you whether your key is right. If the duplicate count is a handful, you probably have genuine double-entries. If a third of the file is "duplicated", the key is wrong: you are matching on something that was never unique.

Then look at a specific case in full, which is the step people skip:

duckdb -c "SELECT * FROM read_csv('input.csv') WHERE email = '[email protected]'"

That is where you find out that the two rows differ in the one column you cared about.

Step three: pick which row survives

Once you know the rows differ, "remove duplicates" becomes "keep the right one". Three rules, in rough order of how often they are correct:

Keep the most recent. If there is a timestamp, this is usually the answer.

duckdb -c "COPY (SELECT * FROM (SELECT *, row_number() OVER (PARTITION BY email ORDER BY updated_at DESC) rn FROM read_csv('input.csv')) WHERE rn = 1) TO 'deduped.csv' (HEADER)"

Keep the most complete. When there is no timestamp, ordering by the number of non-empty fields is a decent proxy, because the fuller record is usually the later one. Order by (col_a IS NOT NULL)::INT + (col_b IS NOT NULL)::INT + … and take the top.

Merge the two. Sometimes each row has something the other lacks. This is common and there is no shortcut: it is a coalesce per column, and for a small set that is better done by hand than automated badly for a large one.

Whichever you pick, write the discarded rows to a second file before deleting them. A dedupe you can't reverse is a dedupe you will regret at some point in the next fortnight.

Near-duplicates, which are most of them

Exact matching misses the majority of real-world duplicates. Normalise first, compare second, and keep the original values.

duckdb -c "SELECT lower(trim(email)) k, count(*) n FROM read_csv('input.csv') GROUP BY k HAVING n > 1"

The normalisations to apply, roughly in order of yield:

• Case. [email protected] and [email protected] are the same mailbox.
• Whitespace. Trailing spaces from a copy-paste are invisible and extremely common. trim() catches leading and trailing; a replace handles the non-breaking spaces that arrive from web forms.
• Punctuation in identifiers. Phone numbers as +44 20 7946 0958, 02079460958 and (020) 7946 0958 are one number. Strip everything that is not a digit before comparing.
• Gmail dots and plus-addressing. [email protected] reaches the same inbox as [email protected]. Whether that counts as a duplicate depends on whether you are deduplicating people or accounts.

What not to do automatically: fuzzy name matching. "Jon Smith" and "John Smith" may be the same person or two colleagues, and a similarity threshold that is right for one file is wrong for the next. Generate the candidate pairs, review them, then act.

CSV File Viewer - Smart CSViPhone & iPad · Android

The SQL and visual filter editors are the two halves of this job: a GROUP BY to find the duplicate keys, then a filter to read every row sharing one, without loading anything into a database first.

What it does

Doing it in a spreadsheet

Excel and Sheets both have a Remove Duplicates command, and it has one property to know before you use it: it keeps the first occurrence and deletes the rest, with no way to say which one is first. Whatever order the sheet happened to be in decides which record survives.

So sort deliberately before running it. Sort by your timestamp descending, and "keep first" becomes "keep newest", which is usually what you meant.

The other spreadsheet approach, a helper column of COUNTIF, is slower but non-destructive, and it shows you the duplicates rather than removing them. On a large file it is also the thing that will hang Excel, because COUNTIF across half a million rows is quadratic.

On a phone

The realistic version of this on a phone is not a full deduplication pass. It is sorting by the column you suspect, scrolling, and seeing the repeats sit next to each other, which is the fastest way to find out whether a file has a duplicate problem at all.

Once you can see them, filtering to one key and reading the handful of rows that share it answers the only question that matters: are these the same record, or two records that happen to share a field?

CSV Editor - Smart CSViPhone & iPad

Sort by any column the way Excel does, then delete rows with the filter safely applied: hidden rows are protected, so a delete never touches data outside the filter. Undo covers everything, which is the property that makes doing this on a phone reasonable.

What it does

Verifying

Three numbers, and they should reconcile:

wc -l input.csv deduped.csv
duckdb -c "SELECT count(*) rows, count(DISTINCT email) keys FROM read_csv('deduped.csv')"

rows and keys must now be equal. If they aren't, the dedupe didn't apply to every group. And input − deduped must equal the number of rows you meant to remove, which you counted in step two. If it removed more than that, the key was wider or narrower than you thought, and the discarded-rows file you saved is now the thing that saves the afternoon.