Duplicates in CSV: finding them and sorting them out
Duplicates are rarely exact copies. More often they are two records about the same thing that differ by a stray space, letter case or one filled-in field — which is exactly why they survive a uniqueness check.
Three kinds of repeats
- Full duplicates — the whole line matches. Usually the result of loading a file twice. Easy to find.
- Duplicates by key — the identifier matches, the content differs. The most dangerous case: it is not clear which record is right.
- Fuzzy duplicates — differ in spelling: “Acme Inc.” and “ACME, Inc”, “J. Smith” and “John Smith”. They are hard to catch automatically, but normalization removes half of the cases.
How to look for them
The tool is grouping. Group by a candidate field and add a “count” measure: everything with a count above one is a repeat.
If the key is composite, group by several fields at once. Typical combinations: email, order ID + line number, SKU + warehouse, customer + billing period.
A quick estimate without grouping: a column's statistics show the number of unique values. If it is smaller than the number of rows, there are repeats for sure, and you see how many at once.
The shortest route is the “Duplicates by fields” tool: tick the key fields, and the repeats appear side by side in the table, with the number of groups and of surplus copies.
Normalize before searching
Before counting repeats, remove the differences that are not repeats:
- stray spaces at the edges — in DBF files and exports from old systems they are almost always there;
- letter case — usually irrelevant for codes and names;
- different spellings of one value:
+1 (555) 123-4567and15551234567.
The first two are handled by parsing and comparison settings. The third needs an expression: group not by the field itself but by the normalized value.
What to do next
Finding repeats is half the work; the rest is deciding which record is right. A few approaches that usually work:
- keep the later one by modification date, if such a field exists;
- keep the more complete one: one of the records has some fields empty;
- check whether the amounts differ: if they do, it is not a duplicate but two different operations with the same number — and the investigation belongs in the source system.
The filtered list of repeats can be exported to a separate file — usually that is what is passed to whoever owns the data in the system.
FAQ
Can I delete duplicates right in the viewer?
Yes. The “Duplicates by fields” tool shows repeats side by side and flags the surplus copies for deletion, keeping the earliest record; flagged rows are left out when the CSV is saved.
How do I find rows that are missing from the second file?
That is a comparison of two tables by key — see how to compare two CSV files.
How many rows can grouping handle?
The limit is half a million distinct groups; the number of source rows does not matter.
Free for personal use. Your file is not uploaded to a server. Windows version — 3.5 MB, no installation: details. Organizations: license.