How to remove duplicate rows from a CSV
Duplicates creep in when exports overlap or lists are merged. How to remove exact duplicates, why near-duplicates survive and how to tidy the file first.
Duplicate rows are one of the most common problems in exported data. Two exports that overlap by a day, a mailing list combined from several sources, a form that was submitted twice: each leaves rows that appear more than once and they inflate counts, double emails and skew totals.
Removing exact duplicates
The CSV cleaner removes rows that are identical in every column, keeping the first copy and the original order. The heading row is never treated as a duplicate. The report tells you how many rows came in, how many went out and what else it changed.
Why some duplicates survive
To a computer, “Ada Lovelace” and “Ada Lovelace “ with a trailing space are different rows. So are rows that differ only in capitals, date formats or a stray character. That is why the cleaner trims spaces before comparing: it is the single most common reason two apparently identical rows don’t match. Differences in capitals or formatting are genuine differences in the data and need a decision about which version is right.
Duplicates from merging
Most duplicate problems start when files are combined. Merge CSV can remove exact duplicates as it merges and matches columns by heading so rows from files with columns in a different order still compare correctly. Adding a source-file column shows where each row came from, which helps when deciding which near-duplicate to keep.
Tidying at the same time
The cleaner also removes blank rows, evens up rows with missing trailing columns and can remove empty columns. If the file shows strange characters, fix the encoding first, as covered in why does my CSV show strange characters. For a file too big to handle in one piece, split it after cleaning.
Deduplicating on one column
Sometimes the rule is one row per email address or customer ID, even if other columns differ. That needs choosing which row to keep, which spreadsheets handle well with their remove-duplicates feature restricted to the key column.
For a related next step see Combine CSV files without repeating headers.
Common questions
Which copy of a duplicate is kept?
The first one, in its original position. Later copies are removed and the order of everything else is unchanged.
Why were some duplicates not removed?
They differ somewhere: an extra space, different capitals, a trailing comma, a slightly different date format. Trimming spaces first catches the most common difference. Differences in capitals or formatting need fixing in the data.
Can I remove duplicates based on one column, like email?
The cleaner removes rows that match in every column. To deduplicate on one column, such as keeping one row per email address, a spreadsheet's remove duplicates feature lets you choose the columns.
Will the header row be removed if it appears twice?
The first row is always kept as the header. A header repeated further down, which happens when files are stacked, counts as a duplicate row only if it matches exactly.