CSV and JSON β where commas, quotes and line breaks go wrong
CSV is a table split by commas. The rule sounds like it fits in one sentence, right up to the moment a value contains a comma of its own. Almost every CSV problem β mangled characters in Excel, a phone number that lost its leading zero, a row that split in half β starts there.
A CSV file is plain text. The first line names the columns and each line below it is a row. RFC 4180 exists to stop every program inventing its own variation, and this tool follows it.
The hard part is what to do when the separator appears inside a value. Addresses contain commas, notes contain line breaks, quotations contain quotes. How those are written down is essentially all there is to the format.
If the value contains this, write it like that
Wrapping in quotes is the one tool available, and a quote itself is written twice.
| In the value | How it is written | Example |
|---|---|---|
| A comma | Wrap the whole field | Busan, Haeundae β "Busan, Haeundae" |
| A line break | Wrap it; the break is part of the value | "1 Main StreetβApt 4" |
| A quote | Double the quote and wrap | He said "hi" β "He said ""hi""" |
| Leading or trailing spaces | Wrap to preserve them | " value " |
| None of the above | Write it plainly | Alice |
A line break inside a field breaks most parsers
The common way to read CSV is to split the text into lines and then split each line on commas. It is fast, simple, and wrong the moment a value contains a line break. This tool used to work that way, and on the input below three columns and two rows became three rows, with the address cut in half and the note gone entirely.
Quotes have to be tracked while scanning the whole text, because a line break inside quotes is data and one outside them ends the row, and nothing else distinguishes the two. The parser now carries that state character by character.
Line breaks inside values are not an edge case. Pressing Alt+Enter inside a spreadsheet cell stores one, and address, note and delivery-instruction columns collect them routinely.
Why non-Latin characters arrive scrambled in Excel
Convert JSON to CSV, open it in Excel, and the text may come out as nonsense. The file is not damaged; Excel read it with the wrong rules.
There is more than one way to store characters as bytes. The web uses UTF-8, where a Korean or Japanese character takes three bytes. Excel on a localized copy of Windows opens a .csv assuming the older regional encoding instead, so the same bytes are decoded as different characters.
The fix is three bytes at the start of the file β EF BB BF, the byte order mark. It says βthis file is UTF-8β, it is invisible, and Excel honours it. That is what this tool's βopen correctly in Excelβ option adds.
Going the other way, Excel's Save As offers CSV UTF-8 alongside plain CSV; the UTF-8 variant is the one that writes the mark.
Keeping 007 and 010-1234 intact
CSV has no types. Everything is text. So converting to JSON means deciding whether 1234 is a number or a string.
This tool converts numbers by default but leaves anything starting with a zero alone. Turning 007 into 7 destroys an employee number, and 010-1234 was never a number in the first place. A leading zero is a reliable signal that the text is an identifier rather than a quantity.
If nothing downstream is going to do arithmetic, turn the option off and every value stays a string, exactly as written.
Where data disappears quietly
In each of these the tool says what it changed instead of moving on.
- β’Two columns with the same name β the second overwrites the first and a whole column vanishes. The later one gets _2 appended.
- β’An unnamed column β the key becomes an empty string, which is awkward to work with. It is named μ΄2 by position.
- β’Rows with different field counts β missing fields are left empty and the count of affected rows is reported.
- β’A byte order mark β the first column name starts with an invisible character and never matches what you search for. It is stripped.
- β’An unclosed quote β everything after it lands in a single field.
- β’Nested objects in JSON β a table cannot hold a table. They are written as JSON text rather than [object Object].
Frequently asked questions
My file uses semicolons, not commas.
In locales where the comma is the decimal separator, Excel saves with semicolons. Choose Custom for the delimiter and enter one. Tab-separated files work the same way.
I copied from a spreadsheet and the fields are separated by tabs.
Copying a cell range puts tab-separated text on the clipboard. Switch the delimiter to Tab and it converts as-is.
My JSON is a single object, not an array.
Paste it anyway. It becomes a one-row table, and the tool says that is what it did.
Is my data uploaded anywhere?
No. The conversion runs entirely in your browser and nothing you paste leaves it.