Somebody sends you a CSV export. You open it and find one giant column of text, names full of é where accents should be, phone numbers missing their leading zeros, and the same customer appearing four times with three different date formats. Welcome to real-world data.
CSV is not really a standard — RFC 4180 exists, but almost nothing enforces it. Every tool exports its own dialect, and Excel actively "helps" by rewriting your values. This guide catalogs the classic horrors and gives you a repeatable workflow to clean any messy CSV in minutes.
What Makes CSV Files So Messy?
Horror 1: Delimiter chaos (the semicolon surprise)
CSV means "comma-separated values" — except when it doesn't. French, German, and most other European versions of Excel export with semicolons, because those locales use the comma as a decimal separator (3,14). So a "CSV" from a Parisian colleague looks like this:
id;name;price
1;Café des Amis;12,50
2;Boulangerie Marcel;8,90
Feed that to a parser expecting commas and you get one column per row — or worse, the decimal commas get interpreted as delimiters and every price splits in half. Tab-separated and pipe-separated files also travel under the .csv extension. Rule one: never assume the delimiter; look at the file first.
Horror 2: Encoding mojibake (é → é)
If you see Café instead of Café, you're looking at UTF-8 bytes decoded as Latin-1 (or Windows-1252). The character é is two bytes in UTF-8 (0xC3 0xA9); read them one-per-character in Latin-1 and you get é. The reverse mistake produces caf� with replacement characters.
What was written (UTF-8): Café, São Paulo, Müller
What you see (as Latin-1): Café, São Paulo, Müller
A related gremlin is the BOM (byte order mark): three invisible bytes (EF BB BF) that Excel puts at the start of UTF-8 files. Parsers that don't strip it will call your first column id instead of id — and suddenly your import maps zero columns.
Horror 3: Excel eats your data
Excel doesn't just display CSVs — it reinterprets them, destructively:
- Leading zeros vanish. Phone number
0612345678becomes612345678. Zip code01234becomes1234. Once saved, the zeros are gone forever. - Things become dates. The gene names SEPT2 and MARCH1 were interpreted as dates so persistently that in 2020 the genetics community renamed the genes (now SEPTIN2, MARCHF1) rather than keep fighting Excel. Your product code
1-2becomes02-Jan. - Long IDs collapse into scientific notation. A 16-digit ID like
8712000012345678displays as8.712E+15, and Excel silently zeroes out digits past the 15th. Credit card numbers, EAN barcodes, and tracking numbers are all casualties.
If a column must stay text, it must be typed as text before Excel ever touches it — after opening, the damage is done.
Horror 4: Quotes, embedded commas, and newlines
A perfectly valid CSV row can contain commas and even line breaks inside quoted fields:
id,name,notes
1,"Smith, John","Called on Monday.
Wants a callback."
That's one record spanning two physical lines. Any script that naively does split(",") per line will shred it. Add unescaped quotes inside fields and the parse can derail for every subsequent row.
Horror 5: Duplicates, date roulette, and invisible characters
- Duplicate rows from double exports, retried API calls, or copy-paste merges quietly inflate counts and corrupt aggregates.
- Inconsistent dates:
03/04/2026is April 3rd in Paris and March 4th in Chicago. MixedDD/MMandMM/DDin one column is unfixable unless some values (day > 12) betray the format. - Trailing whitespace and invisible characters:
"Paris "≠"Paris", and non-breaking spaces (U+00A0), zero-width spaces, or stray tabs are invisible to the eye but fatal to joins and GROUP BYs.
A Step-by-Step CSV Cleaning Workflow
Fix things in the right order — encoding before delimiters, structure before content — or you'll redo work.
Step 1: Profile before you touch anything
Never clean blind. Run the file through a CSV Analyzer first and answer: How many rows and columns? What delimiter? Which columns have empty values, mixed types, or suspicious outliers? Two minutes of profiling routinely reveals that "one messy file" is actually three specific, fixable problems.
Step 2: Fix the encoding
Convert everything to UTF-8 without BOM — the de facto standard that every modern tool accepts. If you see mojibake patterns (é, è, ’), the file needs re-decoding, not find-and-replace: fixing symptoms character-by-character misses the ones you didn't spot.
Step 3: Normalize the delimiter and structure
Standardize on commas with proper quoting (or convert to a saner format entirely — a CSV to JSON conversion makes structure explicit and ends delimiter ambiguity for downstream code). Verify that every row has the same column count; rows that don't are usually quote-escaping casualties from Step 4-type issues.
Step 4: Clean the values
This is where a Data Cleaner does the heavy lifting in one pass: trim leading/trailing whitespace, collapse double spaces, strip invisible characters, normalize casing, and standardize empty values (NULL, N/A, -, "" → one convention). Then normalize dates to ISO 8601 (2026-07-10) — the only format that sorts correctly as text and is unambiguous everywhere.
Step 5: Deduplicate — carefully
Exact duplicate rows are the easy case: sort, then remove identical lines. With line-based data, Sort Lines followed by Remove Duplicate Lines handles it in seconds. The subtle case is near-duplicates — same customer, one row with a trailing space or different casing — which is exactly why deduplication comes after value cleaning, not before.
Step 6: Validate the result
Re-profile the cleaned file and check the numbers make sense: row count dropped by the expected amount, no column went suspiciously empty, date ranges are plausible, IDs still have their full length. Cleaning that isn't validated is just editing.
Before and After
# Before
ID;Name ;signup date;Phone
1;Café élite ;03/04/26;0612345678
1;Café élite;3/4/2026;612345678
2;Müller GmbH;2026-04-03;+49301234567
# After
id,name,signup_date,phone
1,Café élite,2026-04-03,0612345678
2,Müller GmbH,2026-04-03,+49301234567
Same data, one-third the rows, zero ambiguity — and it will import identically everywhere.
Quick-Reference: Symptom → Fix
| Symptom | Likely cause | Fix |
|---|---|---|
| Everything in one column | Semicolon/tab delimiter | Detect delimiter, re-parse |
é, ’, ü in text | UTF-8 read as Latin-1 | Re-decode as UTF-8 |
First header is id | BOM | Strip BOM, save UTF-8 without BOM |
| Zip codes lost leading zeros | Excel typed column as number | Re-export, force text type |
8.7E+15 in an ID column | Excel scientific notation | Re-export as text (digits >15 are lost) |
| Rows randomly split/merged | Unquoted commas or newlines | Use a real CSV parser, fix quoting |
| Counts higher than reality | Duplicate rows | Clean values, then dedupe |
| Joins mysteriously fail | Trailing/invisible whitespace | Trim + strip invisible characters |
Clean Your CSV Now
Messy CSVs aren't bad luck — they're the predictable output of locale differences, encoding mismatches, and Excel's enthusiasm. With a profile-first workflow and the right order of operations, even a disaster file becomes clean, importable data in a few minutes.
Paste your data into the free Data Cleaner — trimming, deduplication, case and format normalization in one pass, entirely in your browser, so your data never leaves your machine. No account required.