How to clean a messy CSV file
Most CSV files need a little cleaning before they are usable. This guide covers the issues that come up most often and how to fix each one.
Trailing and leading spaces
A single trailing space makes two values that look identical fail to match, breaking joins and lookups. Trimming every cell is the first and most valuable step.
Trim before de-duplicating, so rows that differ only by whitespace are correctly recognized as duplicates.
Blank rows and duplicates
Exports often carry fully blank rows that break imports. Remove rows where every cell is empty or whitespace.
Then remove exact duplicate rows, keeping the first occurrence. Check the count to confirm how many were dropped.
Inconsistent headers
Headers like "Full Name (2024)" are unusable as SQL column names. Normalizing to snake_case fixes spaces and capitals, prefixes leading digits, and resolves duplicates.
Encoding and separators
Files saved from Excel in some locales use semicolons instead of commas, and may be encoded in GBK rather than UTF-8. A good tool detects both and tells you what it found.
Do it in your browser
Frequently asked questions
- Can cleaning change the original file?
- No. The original is read in memory; you download a separate cleaned copy.
- Will trimming remove needed spaces?
- Only leading and trailing whitespace is removed; spaces inside a value are kept.
- Does cleaning happen on a server?
- No. It runs in your browser, so sensitive rows stay on your device.