Converting CSV to JSON and back without breaking the data

CSV is a plain text table: one record per line, with fields separated by a delimiter, usually a comma. It breaks when a field holds the delimiter, a double quote or a line break, because the reader must tell structure from content. Follow the quoting rules, check the delimiter and header row, and keep numbers with leading zeros as text. The rest of this article covers what a round trip can and cannot keep.

CSV to JSONNeed CSV as JSON, or backOpen

What a CSV file really is

CSV has no single authoritative standard, but RFC 4180 describes the common form, and most readers follow it. Its core rules fit in a short list:

• Each record sits on its own line, and the RFC says lines end with a carriage return and line feed, CRLF.
• Fields are separated by commas.
• A field that contains a comma, a double quote or a line break is enclosed in double quotes.
• A double quote inside a quoted field is written as two double quotes.

So the row 1001,"Smith, Ann",02134 has three fields, because the comma inside the quotes is content. A splitter that cuts at every comma finds four. The fixmint reader applies these rules: a quoted field may hold the delimiter, line breaks and doubled quotes, and an unclosed quote is reported with the line where it starts.

Why the delimiter changes

The comma is the RFC default, but where the comma is the decimal mark, so 1,5 means one and a half, spreadsheets often save CSV with semicolons. Tab-separated files (TSV) avoid the conflict in most data, because tabs rarely appear inside text.

The Delimiter menu offers Auto, Comma, Semicolon, Tab and Pipe. Auto reads the first ten lines, counts each candidate outside quotes, and picks the one whose count per line is most consistent. Choose the delimiter yourself when Auto guesses wrong.

Converting in fixmint

The worked example uses the default options of the CSV to JSON tool.

Worked example

Paste this CSV, with First row is a header ticked:

id,name,zip,note
1001,"Smith, Ann",02134,"She said ""hi"""
1002,Bo,10001,"line one
line two"

With Convert numbers off and JSON indent set to 2 spaces, the tool returns:

[
  {
    "id": "1001",
    "name": "Smith, Ann",
    "zip": "02134",
    "note": "She said \"hi\""
  },
  {
    "id": "1002",
    "name": "Bo",
    "zip": "10001",
    "note": "line one\nline two"
  }
]

With Convert numbers on, id and zip 10001 become numbers, and 02134 stays text.

Steps

1. Paste the CSV, or drop a .csv, .tsv, .txt or .json file.
2. Check First row is a header, the Delimiter and Convert numbers. Pick a JSON indent of 2 spaces, Tab or Minified.
3. Copy the result, or download it as .json.

Keep numbers as text

JSON numbers cannot have leading zeros, so 02134 is not a valid JSON number. ZIP codes, phone numbers and product codes must stay text, or the zero disappears. With Convert numbers on, the tool converts 10001 but keeps 02134 as a string. The option is off by default. It converts only values with a dot as the decimal point, so 1,5 in a semicolon file stays as typed.

Long identifiers need care too. JavaScript, which runs most browser JSON, stores numbers as 64-bit floating-point values. Whole numbers are exact only up to 9,007,199,254,740,991, which is two to the power of 53 minus 1. The number 12345678901234567890 becomes 12345678901234567000 when a JavaScript program reads it. The tool writes the digits as typed, but a program that parses the JSON in JavaScript will round them. Keep IDs as strings. The JSON formatter also keeps such numbers exactly as typed, and says how many it kept.

Nested JSON and going back to CSV

CSV is flat and JSON can nest. Converting JSON to CSV makes one column for each key across all the objects, in the order each key first appears. A nested object or list goes into its cell as JSON text, a null becomes an empty cell, and true and false are written as words. Rows are joined with CRLF, and a field is quoted only when it holds the delimiter, a quote, a line break or spaces at either end.

Input that starts with [ or { is read as JSON. The chip button switches direction, labelled CSV → JSON or JSON → CSV. In the JSON → CSV direction, the output options show a Delimiter menu and a For Excel option.

A round trip loses information. A null and an empty string both become an empty cell, and nesting does not come back, so keep the original JSON if you need its structure.

Excel, UTF-8 and the byte order mark

A CSV file is bytes, and the reader must know the encoding. UTF-8 holds accented letters and other alphabets. A byte order mark, three bytes at the start of the file, marks the file as UTF-8. Without one, some spreadsheet programs read the file in a local code page, and accented letters turn into odd characters. Spreadsheets also often turn 02134 into 2134 when they open a CSV.

The For Excel option adds the byte order mark to the downloaded CSV only, never to the copied text, and only in the JSON → CSV direction. The reader removes a byte order mark from input, so a file that already has one converts without error.

Edge cases

• Ragged rows. With a header on, a short row is padded with empty values, and a longer row keeps its extra values as column_N. The result reports how many rows differ from the header.
• Repeated or empty headers. A repeated name gets _2, _3 and so on, and an empty name becomes column_N. Both changes stop two keys from overwriting each other.
• Curly quotes. Smart quotes from a word processor are not CSV quotes, so a field such as “Smith, Ann” splits at its comma. Straighten them first, as described in the curly quotes guide.
• Line breaks inside quotes. A field spanning two lines is one record, but remove duplicate lines splits its input at every line break, so it would treat the two halves as separate rows. Convert to JSON and compare the objects, or check by hand. The tool stays useful for one-value-per-line lists.
• Large files. The result box shows the first 100,000 characters, but Copy and the download include the whole output.
• Invalid JSON. The converter reports the line and column of an error, with a caret under the character. The JSON formatter explains the common slips, and the JSON errors guide covers them in more detail.