How to find and remove duplicate rows in a CSV file

Duplicate rows sneak into almost every export: a contact list merged twice, an order system that retried, a report someone appended to by hand. With SQL you can find every duplicate in one query, see how many times each repeats, and save a clean copy. Synth lets you do it in your browser, with nothing to install and without uploading the file anywhere.

Step 1: Load the CSV

Open Synth and drop your CSV onto the page. It becomes a SQLite table named after the file, so customers.csv becomes customers. No account is needed.

Step 2: Find rows that are exact duplicates

Group by every column and keep the groups with more than one row:

SELECT *, COUNT(*) AS copies
FROM customers
GROUP BY name, email, city
HAVING COUNT(*) > 1
ORDER BY copies DESC;

Each row in the result appears more than once in your file, and copies says how many times. If nothing comes back, the file has no exact duplicates.

Step 3: Find duplicates by one column

Often two rows are "the same person" even when other columns differ. To check a single key such as email:

SELECT LOWER(TRIM(email)) AS email, COUNT(*) AS copies
FROM customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1
ORDER BY copies DESC;

LOWER(TRIM(...)) also catches duplicates that differ only by capital letters or stray spaces, which a spreadsheet's "Remove Duplicates" button misses.

Step 4: Keep one copy of each row

For exact duplicates, DISTINCT is all you need:

SELECT DISTINCT * FROM customers;

To keep one row per key, such as the most recent row for each email, number the rows within each group and keep the first:

SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY LOWER(TRIM(email))
           ORDER BY signup_date DESC
         ) AS rn
  FROM customers
)
WHERE rn = 1;

Step 5: Export the clean file

Run the query from step 4 and click Download CSV above the results. With a free account you can also export to Excel. Your original file is never changed.

Don't want to write SQL?

Sign in and ask Synth's AI assistant something like "show me customers whose email appears more than once." It writes the query for you, using only your column names, never your data.

Frequently asked questions