CleanCSV

How to Remove Duplicates from a CSV File (4 Ways)

Updated 3 min read

Duplicate rows creep into CSV files when an export runs twice, lists are merged or a form is submitted more than once. They inflate counts, break imports that require unique values and send the same email to a customer twice.

This guide shows four ways to remove them, from a browser tool that needs no install to a few lines of Python, and explains why some duplicates survive the obvious approach.

First decide what a duplicate is

There are two kinds of duplicates, and the right fix depends on which one you have:

  • Exact duplicates: every cell in the row is identical. These are safe to remove automatically.
  • Duplicate records: the same customer, order or product appears more than once, but other fields differ, such as a newer phone number or a different note. You remove these by comparing only key columns, such as email or order ID, and choosing which copy to keep.

Removing duplicate records always throws some information away, so decide which copy should win: usually the first one (the original) or the last one (the most recent update).

Method 1: In your browser with CleanCSV

This is the quickest way, and the only one here that does not need Excel, a Google account or code. The file stays on your computer.

  1. Open the Remove duplicates tool and drop in your CSV or Excel file.
  2. Remove duplicate rows is already on. For duplicate records, choose Selected columns and pick the key columns.
  3. Tick Ignore capital letters and extra spaces to catch near-duplicates, and choose whether to keep the first or the last occurrence.
  4. Check the preview and the number of removed rows, then download as CSV or Excel.

Method 2: Microsoft Excel

  1. Open the CSV in Excel and click any cell in the data.
  2. Go to Data > Remove Duplicates.
  3. Tick the columns that must match (all of them for exact duplicates), make sure My data has headers is ticked and click OK.

Excel keeps the first occurrence and reports how many rows it removed. It ignores differences in capitalization but not extra spaces, so trim the data first. Also be aware that opening a CSV in Excel can change your data before you start: leading zeros, long numbers and some text values are converted. Read why Excel changes CSV data before saving the file back to CSV.

Method 3: Google Sheets

  1. Import the CSV with File > Import, or open it from Google Drive.
  2. Run Data > Data cleanup > Trim whitespace so trailing spaces do not hide duplicates.
  3. Select the data, choose Data > Data cleanup > Remove duplicates, tick Data has header row, choose the columns to compare and confirm.

To keep the original untouched and get a deduplicated copy instead, put a formula in an empty sheet, such as =UNIQUE(Sheet1!A:F).

Method 4: Python with pandas

For repeatable jobs, a few lines of pandas do the same thing:

import pandas as pd

df = pd.read_csv("contacts.csv", dtype=str)
df["email"] = df["email"].str.strip().str.lower()
df = df.drop_duplicates(subset=["email"], keep="last")
df.to_csv("contacts-clean.csv", index=False)

dtype=str stops pandas from turning IDs and zip codes into numbers, and normalizing the key column first catches near-duplicates. Use keep="first" to keep the original row, or leave out subset to compare whole rows.

Why some duplicates survive

  • Trailing or double spaces: “Ada ” and “Ada” are different text. Trim first.
  • Capitalization: “[email protected]” and “[email protected]” are the same address but different text. Lowercase emails, or compare without regard to case.
  • Invisible characters: non-breaking and zero-width spaces copied from web pages look like normal text. Remove them first.
  • Different formats for the same value: “+1 555 010 2030” and “(555) 010-2030”, or “2024-01-05” and “05/01/2024”. Standardize phone numbers and dates before comparing.
  • Blank keys: when comparing on email, rows without an email should not count as duplicates of each other. CleanCSV keeps them; in Excel, filter them out first.

CleanCSV's quality summary counts near-duplicates, which are rows that only match when case and spacing are ignored, so you can see whether this affects your file before you remove anything.