CSV Formatter

Why a CSV needs formatting at all

Two systems export the same table and you get two files that share not one identical line. One quotes every field, the other quotes only what contains a comma. One ends lines with CRLF because it was written on Windows, the other with LF. One is semicolon-separated because it came out of a European locale. Every one of those files is valid under RFC 4180, and comparing any two of them is miserable.

That is what this page is for. It re-writes the file with one consistent set of conventions so that two exports holding the same data produce the same bytes — which is what makes a diff readable, and what stops a version-control history filling up with changes nobody made.

The rule it works to is narrow and worth stating plainly: a formatter may change format, never a value. Every cell is passed through exactly as it was read. Nothing is trimmed, nothing is re-typed, and no number is ever parsed — which is precisely why 007 is still 007 and a 19-digit ICCID still has 19 digits when it comes out. The only things edited are the characters around a value: its quotes, the delimiter after it, the line ending at the end of its row.

If your file does not parse at all — an unbalanced quote, a delimiter that changes halfway down — this is the wrong tool and it will tell you so rather than guess. CSV Fixer is the one for a broken file. Formatting assumes the file is already readable and only its conventions are a mess.

Cleaning up a file

1. Paste or upload the CSV – Detect works out whether the file is comma, semicolon, tab or pipe separated. Pin it explicitly if the guess looks wrong — the output going to one enormous column is the tell.
2. Choose the quoting style – Only where needed is the one to want: quotes appear on fields containing the delimiter, a quote, a line break, or leading and trailing spaces, and nowhere else. Every field exists because some strict importers insist on it.
3. Set the delimiter and line endings you want out – Converting a semicolon file to commas re-quotes anything that now contains one, so the result stays correct rather than merely looking converted. CRLF is what Windows tooling and older Excel builds expect; LF is what everything else prefers. If a file keeps flipping between the two in your history, Git's end-of-line conversion is usually doing it rather than the exporter.
4. Decide about short rows and blank lines – Padding fills short rows with empty fields so every record has the same field count, which is what most parsers assume. Dropping blank lines removes the stray empty rows that accumulate when a file is edited by hand.
5. Check the counts, then copy or download – The badges show rows, columns and how many short rows were padded. A padded count you did not expect is worth investigating before you ship the file.

The Excel BOM toggle is the one people come back for. Excel needs a byte-order mark to open a UTF-8 CSV without mangling accented characters, but almost every other tool treats that mark as part of your first column name, so id quietly stops matching id. Turn it on for a file destined for Excel and off for one destined for a parser.

Same data, four inconsistencies removed

The input quotes at random, mixes line endings, uses semicolons and has one short row. Nothing in the output holds a different value — only the conventions changed.

id;msisdn;plan;price
"SUB-1001";447700900142;Unlimited 5G, 24 month;29.50
SUB-1002;447700900458;"Data 20GB";15.00

"SUB-1003";447700900773;Business 200GB

id,msisdn,plan,price
SUB-1001,447700900142,"Unlimited 5G, 24 month",29.50
SUB-1002,447700900458,Data 20GB,15.00
SUB-1003,447700900773,Business 200GB,

When this earns its keep

Making two exports diffable

Run last month's file and this month's through the same settings and the diff shows what actually changed, instead of every line being marked as modified because the quoting drifted. This is the single most common reason to reach for it.

Preparing a file for a strict importer

Plenty of import tools reject ragged rows outright, and some insist on quoted fields throughout. Rather than arguing with the exporter, normalise the file to whatever the importer wants.

Undoing a locale mismatch

A colleague sends a semicolon-separated file from a European Excel and your tooling expects commas. Converting the delimiter here re-quotes anything that contained a comma, which is the step a find-and-replace would skip and corrupt the file doing so.

Cleaning up before converting

A consistent file converts more predictably. If the next step is CSV to JSON or reading it as a table, formatting first removes a whole class of surprises.

What it will and will not touch

• Cell values are never modified. No trimming, no type inference, no number parsing. This is verified rather than intended: every cell is compared byte-for-byte after a reparse across every combination of quoting style and delimiter.
• Minimal quoting follows RFC 4180. A field is quoted when it contains the delimiter, a double quote, a line break, or leading and trailing whitespace — the last one because spreadsheets strip unquoted whitespace on import, so quoting is the only way to keep it.
• Embedded quotes are escaped by doubling, as the spec requires, not with a backslash.
• All three line endings are read — CRLF, LF and bare CR — and one consistent ending is written.
• The byte-order mark is handled explicitly. Stripped on read, and re-emitted only if you ask. The Unicode FAQ on byte-order marks explains why it is optional in UTF-8 and why tools disagree about it.
• A truncated file is refused, not guessed at. If the source ends inside a quoted field you get an error and your original text back.
• Nothing is uploaded. The whole thing runs in this tab.

Questions people actually ask

How is this different from the CSV Fixer?

Different problem. CSV Fixer is for a file that will not parse — an unbalanced quote, rows that disagree with the header, a delimiter that changes partway down. This page assumes the file already parses and only its conventions are inconsistent. Everything here is deterministic: the same input and settings always give the same bytes.

Why did quotes disappear from my file?

Because they were not needed. "1","2" and 1,2 are the same two values to any conformant reader, and minimal quoting writes the shorter form. If your consumer genuinely requires quotes everywhere, switch Quoting to Every field.

Why is one of my fields quoted when nothing looks special about it?

It almost certainly has a leading or trailing space. That is legal unquoted but every spreadsheet silently strips it on import, so the quotes are what preserve it. If the space is not meant to be there, the value itself needs fixing — this page will not remove it for you, because removing it would be changing your data.

Will it fix a row with too many fields?

No. Padding adds empty fields to rows that are too SHORT, which is safe because nothing is invented. A row with too many fields usually means an unquoted delimiter inside a value, and silently dropping the extras would destroy data — that is a repair job, not a formatting one.

Does it sort or deduplicate rows?

Deliberately not. Row order is data, and two identical rows may both be meaningful. Formatting changes presentation only. Sorting and filtering live on CSV to Table, where they do not rewrite your file.

What about very large files?

It runs in your browser on the main thread, so a file in the tens of megabytes will make the tab think for a moment. For anything routine it is instant. Nothing is sent anywhere regardless of size, which is the trade being made.

Related tools

Worth reading

• RFC 4180 — the CSV specification – Six pages, and the source of every quoting rule this page applies
• W3C — Model for Tabular Data on the Web – What a CSV file leaves unsaid, and why two readers can disagree about the same file