How to remove duplicate rows from a CSV file

A whole-row duplicate matches the row above it in every column. A key duplicate repeats one column, like email, while other columns differ. If the same customer appears twice with two timestamps, a whole-row check keeps both, so for key duplicates you also pick which row survives: the latest update or the first one in the file.

Drop your CSV on https://rows.page and type SQL in the search box; the table is called data. This lists every email that appears more than once:

SELECT email, count(*) AS cnt
FROM data
GROUP BY email
HAVING count(*) > 1
ORDER BY cnt DESC

To find whole-row duplicates, group by every column. Leave out __rid, the hidden row number rows.page adds to each row, or every row counts as unique:

SELECT * EXCLUDE (__rid), count(*) AS cnt
FROM data
GROUP BY ALL
HAVING count(*) > 1

Run one of the queries below, then open the Export menu and choose CSV. The export has the header row, commas and UTF-8 without a BOM, and __rid is left out.

Drop exact duplicate rows

SELECT DISTINCT * EXCLUDE (__rid) FROM data

DISTINCT doesn't keep the original row order. To keep each row where it first appeared, use this instead:

SELECT * EXCLUDE (first_rid)
FROM (SELECT * EXCLUDE (__rid), min(__rid) AS first_rid FROM data GROUP BY ALL)
ORDER BY first_rid

Keep the latest row per key

SELECT * EXCLUDE (__rid)
FROM data
QUALIFY row_number() OVER (PARTITION BY email ORDER BY updated_at DESC) = 1
ORDER BY __rid

Keep the first row per key

SELECT * EXCLUDE (__rid)
FROM data
QUALIFY row_number() OVER (PARTITION BY email ORDER BY __rid) = 1
ORDER BY __rid

The final ORDER BY __rid puts the surviving rows back in file order. For a key made of several columns, list them all after PARTITION BY. To match keys regardless of case and stray spaces, such as Ann@x.com and ann@x.com, partition by lower(trim(email)) instead of email.

A CSV over 1.5 GB loads only partly in rows.page, with the note "Loaded the first N rows: the file is X, more than the browser can hold." SQL results stop at 1,000,000 rows. Past either limit, run the DuckDB command-line tool on your computer, where there is no __rid column:

COPY (SELECT DISTINCT * FROM 'in.csv') TO 'out.csv' (HEADER);

Like DISTINCT in rows.page, this doesn't keep the original row order. To keep the latest row per key instead, use the same QUALIFY clause on the file:

COPY (
  SELECT * FROM 'in.csv'
  QUALIFY row_number() OVER (PARTITION BY email ORDER BY updated_at DESC) = 1
) TO 'out.csv' (HEADER);

Excel

Select the table and click Data > Remove Duplicates. Tick every column to drop exact duplicates, or only your key columns to drop key duplicates. Excel keeps the first occurrence, so sort newest first beforehand if you want the latest row to survive.

rows.page doesn't open .xlsx or .xls files. Copy the cells in Excel or Google Sheets and click Paste data instead; they arrive tab-separated and open as a table.

awk

This prints each line the first time it appears, so the header stays on top and the order is kept:

awk '!seen[$0]++' in.csv > out.csv

sort -u

sort -u in.csv also drops duplicate lines, but it sorts the header into the data. Keep the header out of the sort:

(head -n 1 in.csv && tail -n +2 in.csv | sort -u) > out.csv

Both awk and sort compare raw text, so a stray space or different quoting makes two rows look different, and they can't keep the latest row per key.