What the CSV cleaner checks
A CSV file is plain text: one record per line, fields separated by a delimiter, and double quotes around fields that contain the delimiter, a quote or a line break. That is why so many files go slightly wrong: an export uses semicolons, a hand edit leaves a row one field short, a copy from a web page brings spaces and blank lines. Import tools then reject the file or load values into the wrong columns.
The cleaner first shows a report of what it found, with line numbers where they help. Then you choose the fixes, check the preview of the first 100 rows and download the clean copy. Your original file is not changed.
| Problem | What the fix does |
|---|---|
| Unknown or unwanted delimiter | Detects comma, semicolon, tab or pipe and writes the one you pick |
| Encoding: byte order mark, Windows-1252 bytes | Reports both, reads the text correctly and writes UTF-8, with or without the mark |
| Garbled letters like é or ’ | Turns them back into é and ’ when the bytes form valid UTF-8 |
| Rows with more or fewer fields than the header | Lists them, then fills short rows with empty fields or removes them |
| Spaces before or after values | Trims them |
| Empty rows, empty columns | Removes them |
| Exact duplicate rows | Keeps the first, removes the rest |
| Mixed CRLF and LF line breaks | Writes one kind throughout |
| Fields with commas, quotes or line breaks | Quotes them and doubles inner quotes |
| Cells that start with =, +, -, @ | Adds a leading apostrophe so they cannot run as formulas |
The fixes in detail
Delimiter. Detect tries comma, semicolon, tab and pipe and picks the one that gives the same number of fields on most of the first rows. If it guesses wrong, set it by hand. The clean file can use another delimiter.
Encoding. The file is read as bytes. A UTF-8 byte order mark (the bytes EF BB BF) is reported and removed from the data. The rest is checked with a strict UTF-8 decoder; if any byte sequence is not valid UTF-8, the file is read as Windows-1252, the usual encoding of older Western exports, and the report says so. You can switch between the two by hand. The clean file is always written as UTF-8.
Rows of the wrong length. RFC 4180, the document that describes the common CSV format, says each line should contain the same number of fields. Fill up short rows adds empty fields at the end. Fields past the header that are empty, often from a delimiter at the end of a line, are cut; if a row still has more values than the header, the header gets extra columns named Column 5, Column 6 and so on, so no value is lost. Remove them drops every row whose length differs from the header.
Spaces, empty rows and columns, duplicates. RFC 4180 counts spaces as part of a field, so trimming changes the data on purpose: spaces, tabs, line breaks and non-breaking spaces at the start and end of a cell go. A row counts as empty when every cell in it is empty or only spaces, a column when every cell including the header is empty. Duplicates are rows that are exactly the same after the steps before, header excluded. Blank lines are always left out. To remove duplicates from a plain list instead, use Remove Duplicate Lines.
Quoting and line breaks. Following rules 6 and 7 of RFC 4180, a field is put in double quotes when it contains the delimiter, a double quote or a line break, and a double quote inside it is written twice. Every record ends with the line break you choose; RFC 4180 uses CRLF. Line breaks inside quoted fields stay as they are.
Formula injection. The OWASP page on CSV injection explains that a cell beginning with =, +, -, @, a tab, a carriage return or a line feed (and their full-width forms) can be run as a formula when the file is opened in a spreadsheet, and lists a leading single quote as a defense. The cleaner adds that apostrophe. A cell that is only a number, like -12.5 or +3, holds no formula, so it is left alone unless you clear that option.
Opening the clean file in Excel
Microsoft notes that a UTF-8 CSV saved with a byte order mark opens normally in Excel (Opening CSV UTF-8 files correctly in Excel), which is why the mark is on by default. Leave it off for files that go to a database or a script; the Unicode FAQ notes that some programs receiving UTF-8 do not expect it. If letters still look wrong, why CSV files show wrong characters in Excel walks through the causes and the import route.
Excel also removes leading zeros and keeps only 15 significant digits of a number. Microsoft's way around it is to import the file with Data, From Text/CSV and set those columns to Text (Keeping leading zeros and large numbers). Quoting numbers with leading zeros helps import tools that read quoted values as text, but whether a program does that depends on the program. CSV to Excel writes a workbook that keeps the zeros, CSV to JSON turns the clean file into data for code, and Split CSV cuts a large one into parts. For when CSV is the right format at all, see JSON vs YAML vs CSV.
Limits and privacy
Files up to 50 MB can be opened. The whole file is held in memory while you work, so large files take a few seconds and can fail on phones and tablets, iPhone and iPad included. The cleaner fixes the structure of a file, not its content: it does not check that a date is a date or that an email address is valid. Everything runs in your browser; the file is not uploaded and nothing is stored after you close the page.
How to clean a CSV file
- 1Paste CSV text, or open or drop a file. The report shows what the file has.
- 2Pick the fixes and the delimiter and line breaks for the clean file. The preview updates as you change them.
- 3Press Download CSV, or copy the result.
Sources
- RFC 4180: Common Format and MIME Type for Comma-Separated Values (CSV) Files, section 2
- OWASP: CSV Injection
- Microsoft Support: Opening CSV UTF-8 files correctly in Excel
- Unicode FAQ: UTF-8, UTF-16, UTF-32 and BOM
- Microsoft Support: Keeping leading zeros and large numbers
Related guides
- CSV Shows Wrong Characters in Excel: The UTF-8 BOM Fix
- JSON vs YAML vs CSV: Which Format for Which Job
- How to Pick a Random Winner Fairly: Draws People Trust
Frequently asked questions
What does the CSV cleaner fix?
It finds the delimiter and the encoding, lists rows with too many or too few fields, trims spaces around values, removes empty rows, empty columns and exact duplicate rows, writes one kind of line break, quotes fields that need it and neutralizes cells that start like a spreadsheet formula. Each fix is a checkbox, so you decide what changes.
Why do some cells get an apostrophe in front?
A cell that starts with =, +, -, @, a tab or a line break can run as a formula when the file is opened in a spreadsheet. OWASP describes this as CSV injection and recommends a leading apostrophe, which makes the spreadsheet show the value as text. Cells that are only a number, like -12.5, are left alone unless you turn that option off.
My file shows strange characters like é instead of é. Can this fix it?
Often, yes. That pattern appears when UTF-8 text was read as Windows-1252 and saved again. The cleaner finds such cells and turns them back, but only when the result is valid UTF-8, so correct text is not touched. If the whole file looks wrong instead, switch Encoding between UTF-8 and Windows-1252.
Should I keep the byte order mark?
For files you open in Excel, yes: Microsoft notes that a UTF-8 CSV saved with a byte order mark opens normally. For files that go to a database, a script or an API, leave it off: the Unicode FAQ notes that some programs receiving UTF-8 do not expect the mark.
Does quoting keep leading zeros in ZIP codes?
It marks the value as one field, which some import tools read as text, but it depends on the program. To be sure in Excel, import the file with Data, From Text/CSV and set the column type to Text, or turn the CSV into a workbook with CSV to Excel, which keeps the zeros.
Is my file uploaded?
No. The file is read and cleaned by your own browser, and the cleaned copy is made on your device. Nothing is sent to a server.