How to Clean a Messy CSV File: Duplicates, Spaces and Empty Rows
CSV files from exports, forms and old systems are rarely clean. Extra spaces, duplicate rows, empty lines and inconsistent headers break imports, double-count results in reports and make lookups fail. Here is a practical checklist, and how to do each step online, in Excel and in Python.
The cleaning checklist
| Problem | Why it matters |
|---|---|
| Spaces before or after values | "Ann " and "Ann" look the same but do not match in lookups or joins |
| Duplicate rows | Totals and counts come out too high |
| Empty rows | Imports fail or create blank records |
| Empty columns | Clutter, and sometimes unnamed columns that confuse tools |
| Uneven rows (too many or too few fields) | Data shifts into the wrong columns |
| Inconsistent headers | First Name, first_name and FirstName do not map to the same field |
| Line breaks inside cells | Some tools treat them as new rows |
| Wrong delimiter | Everything ends up in one column |
Method 1: Online CSV cleaner (fastest)
CSV Cleaner fixes all of the problems above in one pass. It detects the delimiter automatically, so comma, semicolon, tab and pipe files all work.
- Paste the CSV or open the file.
- Choose the fixes: trim spaces, remove empty rows, remove duplicates, remove empty columns, fix uneven rows, and optionally convert headers to
snake_caseorcamelCase. - Check the summary (for example "12 duplicates removed, 3 empty rows removed") and the preview table.
- Pick the output delimiter and download the cleaned file.
The file is processed in your browser and never uploaded, which is important for customer lists and other personal data.
Method 2: Excel
- Duplicates: select the data, then Data โ Remove Duplicates, and choose which columns must match.
- Spaces: in a helper column use
=TRIM(A2), copy, and paste as values over the original.TRIMalso turns double spaces inside the text into single spaces. - Empty rows: select a column, then Home โ Find & Select โ Go To Special โ Blanks, and delete the entire rows. Check first that the column you picked is only empty on truly empty rows.
- Headers: rename them by hand. There is no built-in bulk rename.
Method 3: Python with pandas
import pandas as pd
# sep=None + engine="python" detects the delimiter; dtype=str keeps leading zeros
df = pd.read_csv("contacts.csv", sep=None, engine="python", dtype=str)
# Trim spaces in every text cell
df = df.apply(lambda col: col.str.strip())
# Treat empty strings as missing, then drop fully empty rows and columns
df = df.replace("", pd.NA).dropna(how="all").dropna(axis=1, how="all")
# Remove exact duplicate rows
df = df.drop_duplicates()
# Normalize headers: "First Name " -> "first_name"
df.columns = (
df.columns.str.strip().str.lower().str.replace(r"[^a-z0-9]+", "_", regex=True).str.strip("_")
)
df.to_csv("contacts_clean.csv", index=False)A note on "duplicates"
Removing duplicates finds rows that are exactly the same. A row with "ann@mail.com " (with a trailing space) only matches "ann@mail.com" after trimming, so trim first, then remove duplicates. The CSV Cleaner applies its fixes in that order. Differences in upper and lower case (Ann@Mail.com) still count as different values, so normalize case first if it should not matter.
If you want to treat rows as duplicates when only one column matches, such as the same email with different names, that is a decision about your data. Use drop_duplicates(subset=["email"]) in pandas, or pick that column in Excel's Remove Duplicates dialog.
After cleaning
- Need the data in an API or app? Convert it with CSV to JSON.
- Opening it in Excel? If everything lands in one column, see why your CSV opens in one column, or download a real
.xlsxwith CSV to Excel.