CSV Cleaner

about this tool

The four things that go wrong

A CSV is a simple format that almost nothing implements the same way, and broken ones break in the same handful of ways.

The encoding. A byte-order mark settles it; without one, the file is tried as UTF-8 and falls back to Windows-1252, which is what a spreadsheet on Windows writes. If the text was already decoded wrongly somewhere upstream, an accented letter appears as two characters and a curly apostrophe as three. That is detected by trying the repair in reverse: putting each character back to the byte it came from and reading the result as UTF-8. Only text that really was mis-decoded survives that, which makes it a test rather than a guess.

The delimiter. Commas, semicolons, tabs and pipes are counted on every line, ignoring anything inside quotes, and the winner is whichever appears the same number of times on the most lines. A delimiter is consistent; a character that merely occurs in the prose is not. Much of Europe writes semicolon-separated files because the comma is the decimal point.

The quoting. Reading follows RFC 4180: a quote inside a quoted field is doubled, and a newline inside quotes belongs to the field. The two common mistakes — a quote opened and never closed, and a quote in the middle of an unquoted field — are reported with their line numbers rather than silently swallowing the rest of the file.

The shape. Rows with too few or too many fields, columns with nothing in them, rows that repeat, a column name used twice, and fields padded with spaces are all counted, and each can be fixed or left alone.

Writing it back

The separator, the line ending and the quoting are yours to choose. Quote every field if whatever reads it next is fussy; otherwise only the fields that need it are quoted, which is what the specification asks and what keeps the file readable.

The byte-order mark deserves its own note. Excel on Windows assumes the local code page unless a file starts with one, which is why an accented name so often arrives wrong. Three bytes at the front fix it, and the option is turned on automatically for a file that arrived as Windows-1252.

Limits

This is for repairing a file, not for exploring one — the CSV and Excel viewer is the tool for reading a large table, and it opens XLSX too. Everything is held in memory as text, so a file of hundreds of megabytes will not do. Fields are not converted or validated: a date stays whatever text it was. Nothing is uploaded and nothing is stored between visits.

Once the file parses, the CSV & Excel viewer reads it as a sortable table and CSV to JSON converts it for a program. For row-level tidying — deduplicating, sorting, dropping blanks — sort & clean up lines works on the raw text.

questions

Why do accented letters come out as two wrong characters?

Because a UTF-8 file was read as a single-byte encoding, usually Windows-1252. One letter is two or three bytes in UTF-8, and each byte gets shown as its own character. The tool spots it by trying the repair in reverse, and offers to put it back.

Why does Excel mangle my UTF-8 file?

Excel on Windows assumes the local code page unless the file starts with a byte-order mark. Tick that option when saving and the same file opens correctly. It is three otherwise pointless bytes at the front, and it is the difference between a name being right and wrong.

How does it know which character separates the fields?

It counts commas, semicolons, tabs and pipes on each line, ignoring anything inside quotes, and picks the one that appears the same number of times on the most lines. A delimiter is consistent; a character that merely occurs in the text is not. When nothing is consistent it says it is guessing, and you can override it.

What does it do about broken quotes?

It reports them with the line number rather than quietly swallowing the rest of the file. A quote opened and never closed, and a quote in the middle of an unquoted field, are both mistakes a reader has to make a decision about; this one tells you what it decided.

Is my file uploaded?

No. It is decoded, parsed, repaired and written in this page. Nothing leaves your device and nothing is stored between visits.