📋 Contents
The 7 Most Common CSV Problems
If you've ever imported a CSV into a database, opened one in a spreadsheet, or tried to join two CSVs together, you've hit these problems. They're universal. Here's the full list with the fix for each:
| Problem | What it looks like | Why it happens |
|---|---|---|
| 1. Duplicate rows | Same record appears 2+ times | Export from multiple sources, appending without dedup |
| 2. Trailing/leading whitespace | "New York " doesn't match "New York" | Human data entry, Excel formatting |
| 3. BOM character | First column name has invisible prefix | Saved as "UTF-8 with BOM" from Excel on Windows |
| 4. Encoding mismatch | José becomes José or Jos? | Latin-1 saved as UTF-8, or vice versa |
| 5. Inconsistent nulls | Empty field written as NULL, N/A, --, "", or blank | Multiple people, systems, or exports contributing data |
| 6. Comma/quote inside fields | Row splits in wrong place, fields merge | Unescaped commas in text fields like addresses |
| 7. Mixed date formats | 01/02/2026 — Jan 2 or Feb 1? | US vs EU date formats in the same column |
Removing Duplicate Rows — The Right Way
Not all "duplicates" are actually duplicates. There are three levels:
1. Exact duplicates (all columns match)
These are safe to delete. The row is literally identical. Use the Remove Duplicates tool — it compares entire rows, keeps the first occurrence, deletes the rest.
2. Key-based duplicates (one column matches)
Two rows have the same email or ID but different other fields. This is trickier — you need to decide which row to keep. Maybe the one with the most recent timestamp? The most complete set of fields? Key-based dedup requires merge logic, not just deletion.
3. Fuzzy duplicates (similar but not identical)
"John Smith" vs "John Smyth" vs "Jon Smith". Same person, slightly different spelling. Fuzzy matching (Levenshtein distance, Soundex) helps, but automated fuzzy dedup is risky. Review manually or set a high similarity threshold (>95%).
Invisible Whitespace That Breaks Joins
This is the most frustrating CSV problem because you can't see it. Your VLOOKUP returns #N/A. Your SQL JOIN returns zero matches. The values look identical. They're not.
Common whitespace issues:
- Leading spaces:
" New York"— space before the value. Happens when data entry people hit spacebar before typing. - Trailing spaces:
"New York "— space after. Even harder to spot. - Non-breaking spaces:
- Tabs instead of commas: File looks CSV but delimiter is actually tab. TSV masquerading as CSV.
Our CSV Cleaner automatically trims whitespace from every field. For non-breaking spaces, it normalizes all Unicode whitespace characters to standard spaces first, then trims. If you're cleaning in code, use .replace(/\s+/g, ' ').trim() — the \s class catches non-breaking spaces too.
Encoding Hell: UTF-8, BOM, and Garbled Text
CSV encoding problems come from one fact: CSV has no built-in way to declare its character encoding. A CSV file is just bytes. The reader guesses. When the guess is wrong, you get garbled text.
The BOM Problem
Windows Excel writes CSVs as "UTF-8 with BOM" by default. BOM (Byte Order Mark) = three invisible bytes (EF BB BF) at the start of the file. Most non-Microsoft tools don't expect these bytes. Result: the first column header gets a prefix.
// BOM corruption example Expected header: "id","name","email" Actual header: "id","name","email" // SQL query fails: column "id" not found // JSON key becomes "id" instead of "id" // VLOOKUP can't match "id" because it's actually "id"
The fix: strip the BOM. Our CSV Cleaner does this automatically. In code: check if the first three bytes are 0xEF, 0xBB, 0xBF — if so, remove them before parsing.
UTF-8 vs Latin-1
If accented characters (é, ñ, ü) appear as two garbled characters like é, your file is UTF-8 being interpreted as Latin-1 (ISO-8859-1). If they appear as ?, your file is Latin-1 being interpreted as ASCII. The fix: explicitly set encoding when opening, or convert the file with a tool.
The Fastest Workflow for Cleaning Any CSV
- Open the CSV in a text editor first (not Excel). Check the raw data. Are fields quoted? What delimiter? Any obvious garbage in the first few rows?
- Remove duplicate rows — use Remove Duplicates for exact dedup.
- Clean whitespace and nulls — use CSV Cleaner to trim all fields, standardize empty values.
- Convert if needed — CSV to JSON or JSON to CSV if you're switching formats.
- Validate — count rows before and after each step. If a cleaning operation changed the row count, investigate. It shouldn't (except dedup).