Converting between CSV and JSON without losing data: quoting, types and nested objects

CSV has a standard, and it is short

RFC 4180, published in 2005, describes the common CSV format in a few pages. Each record is one line ending in a carriage return and line feed, fields are separated by commas, and the first line may be a header. A field that contains a comma, a double quote or a line break must be enclosed in double quotes, and a double quote inside such a field is written twice.

Most of the trouble with CSV comes from files that ignore those rules or from tools that apply them partially. A spreadsheet exported in a locale that uses the comma as the decimal separator often switches the field separator to a semicolon. Other exports use tabs. A converter that assumes commas produces a single column holding every value, which is why good tools detect the delimiter from the first lines before parsing.

Line breaks inside quoted fields are legal and common in free-text columns such as comments or addresses. Any approach that splits the file on newlines first and on commas second will cut those records in half. A correct parser reads character by character and tracks whether it is inside quotes.

• Quote a field if it contains the delimiter, a double quote, a carriage return or a line feed.
• Double every double quote inside a quoted field.
• Expect semicolons and tabs as delimiters in files from other locales.
• Never split a CSV file on newlines before parsing quotes.

Types do not exist in CSV, so a converter has to guess

Every CSV field is text. JSON distinguishes strings, numbers, booleans and null, so a converter either keeps everything as strings or infers a type for each cell. Inference is convenient, since numeric columns become numbers a program can add up, but it is a guess, and it guesses cell by cell rather than column by column.

A converter that guesses cell by cell turns 02134 into text, because a number would lose the leading zero, and 10001 into a number, because it looks numeric. The result is a zip column holding a string in one record and a number in the next, which breaks typed code downstream. The CSV to JSON converter on this site decides per column instead: a column becomes numbers only when every value in it is a plain number that JSON can carry exactly, and booleans only when every value is true or false. The example below is its real output; the zip column stays text in both records because one value starts with zero.

input CSV
id,name,zip,active,note
1,"Smith, Jane",02134,true,
2,Ali,10001,false,"said ""hi"""

output JSON
[
  { "id": 1, "name": "Smith, Jane", "zip": "02134", "active": true,  "note": "" },
  { "id": 2, "name": "Ali",         "zip": "10001", "active": false, "note": "said \"hi\"" }
]

Worth knowing. Integers longer than 15 or 16 digits are kept as text for the same reason: JavaScript would round them. If a column must be text regardless of its contents, such as phone numbers written without separators, turn type inference off.

Numbers that are not safe in JSON

JSON itself places no limit on the size or precision of a number, but RFC 8259 warns that software commonly stores numbers as IEEE 754 double precision values. Integers beyond 9007199254740991 cannot all be represented exactly in that format, so a 19-digit account number or a 64-bit database identifier read into JavaScript is silently rounded to a nearby value.

The same problem appears with spreadsheets, which also store numbers as doubles and display long values in scientific notation. A card number or tracking code that passed through a spreadsheet as a number has usually lost its last digits before it ever reaches a converter. The only reliable fix is to keep such values as strings in both formats from the start.

Decimal values have a related issue. A price such as 0.1 cannot be stored exactly in binary floating point. Converting it once is harmless, but code that sums many converted prices should use integer minor units, such as cents, or a decimal library rather than doubles.

Flattening nested JSON into columns

CSV is flat: every record has the same columns and every cell holds one value. JSON from an API is usually nested, with objects inside objects and arrays of varying length. Going from JSON to CSV therefore requires a flattening rule, and the rule chosen decides whether the data can be rebuilt later.

The JSON to CSV converter on this site uses path-style headers. A nested field becomes a column named with a dot, such as user.name, and each array element gets its own column with an index in brackets. The example below shows a record with a nested object, a two-item array and a note containing a line break, which the converter quotes as the standard requires.

input JSON
[{"id":1,"user":{"name":"Jane","tags":["a","b"]},"note":"line1\nline2"}]

output CSV
id,user.name,user.tags[0],user.tags[1],note
1,Jane,a,b,"line1
line2"

Worth knowing. Index columns grow with the longest array in the file. If one record has fifty tags, every row gets fifty tag columns, most of them empty. For arrays of unbounded length, a separate CSV file with one row per element is usually the better shape.

Encoding, byte order marks and Excel

RFC 4180 does not fix a character encoding, and JSON as exchanged between systems must be UTF-8 according to RFC 8259. Most CSV problems with accented names or non-Latin scripts come from a file saved in a legacy code page and read as UTF-8, or the reverse, which turns letters into sequences of odd symbols.

Microsoft Excel adds a twist. When it opens a CSV file by double-click, it assumes the local legacy encoding unless the file begins with a UTF-8 byte order mark, the three bytes EF BB BF. Adding the mark fixes Excel but can confuse other tools that do not expect it, where it appears as an invisible character glued to the first header name. A converter that offers the mark as an option, rather than always adding or always omitting it, lets the user choose based on where the file is going.

Excel also interprets cells as it opens them: long numbers become scientific notation, values that look like dates become dates, and a leading zero disappears. Importing through the data import dialog and marking the affected columns as text avoids those changes.

A checklist for a lossless round trip

A conversion is safe when converting back produces the original records. The practices below cover the failures shown in this article and apply whichever converter is used.

• Confirm the delimiter and encoding before converting, not after.
• Treat identifiers, postal codes, phone numbers and long integers as text.
• Check that every column in the JSON output has a single type.
• Keep nested JSON as JSON when the structure matters; flatten only for spreadsheets and reporting.
• Validate the JSON result with a formatter before sending it to an API.
• Generate a schema or TypeScript types from a sample to catch type drift early.