Converting CSV to JSON (and Back) Without Losing Data

CSV looks simple until a comma shows up inside a field. A practical guide to RFC 4180, headers, types and the edge cases that break naive converters.

CSV is the format every system can export and no two systems export quite the same way. Converting it to JSON looks like a five-line job: split on newlines, split on commas, zip with the header. That works right up until a customer's address contains a comma.

This guide covers how CSV is really structured, where naive converters go wrong, and how to think about types when moving between CSV and JSON. The CSV to JSON Converter handles the simple everyday cases in your browser.

An old format with a late standard

Comma-separated values predate personal computers. IBM's Fortran compilers supported list-directed input with commas in the early 1970s, and spreadsheets from VisiCalc onwards used similar formats for import and export. CSV spread because it was simple and human-readable, and every program implemented it slightly differently.

The closest thing to a standard is RFC 4180, published in 2005. It is an informational RFC, and it says plainly that it documents the most common existing practice rather than defining a strict format. Its rules are:

  1. Each record is on its own line, ending in CRLF.
  2. An optional header line has the same format as records.
  3. Fields are separated by commas, and each line should have the same number of fields.
  4. Fields containing commas, double quotes or line breaks must be enclosed in double quotes.
  5. A double quote inside a quoted field is escaped by doubling it.

The cases that break naive converters

Here is a perfectly valid RFC 4180 file:

id,name,address,notes
1,Ada Lovelace,"12 St James's Square, London","Said ""hello"""
2,Alan Turing,"Bletchley Park
Milton Keynes",

A converter that splits on commas and newlines will get almost every row wrong:

  • Row 1's address contains a comma inside quotes.
  • Row 1's notes contain escaped quotes.
  • Row 2's address contains a line break inside quotes.
  • Row 2 ends with an empty field.

Handling these properly needs a small state machine that tracks whether it is inside a quoted field. Mature libraries do this for you: Papa Parse in JavaScript, Python's built-in csv module, encoding/csv in Go. The RedBison converter is deliberately simple and splits on commas, so it suits data without quoted fields, which in practice covers a lot of quick spreadsheet exports and test data. For files like the one above, use one of those libraries.

Dialects you will meet

VariationWhere it comes from
Semicolon separatorExcel in locales where the comma is the decimal separator (much of Europe)
Tab separator (TSV)Database exports, copying cells from spreadsheets
LF instead of CRLFUnix tools and most programming languages
UTF-8 BOM at startExcel's "CSV UTF-8" export
No header rowMany machine-generated logs and exports

The byte order mark deserves a special mention. Excel adds three invisible bytes at the start of UTF-8 CSV files, so the first header can come out as \uFEFFid instead of id, and code that looks up row.id gets undefined. If the first column misbehaves, that is the likely cause.

The type problem

CSV has no types. Every value is text. JSON has strings, numbers, booleans and null. Moving between them forces decisions:

  • Should 007 become the number 7? Not if it is a product code or a ZIP code.
  • Should 1e5 become 100000? Probably not if it is a part number.
  • Should an empty cell be "", null, or a missing key?
  • Should TRUE, yes and 1 all become true?
  • Large IDs like 9007199254740993 cannot be represented exactly as JavaScript numbers, so they must stay strings.

Spreadsheets make their own guesses, sometimes destructively. In 2020 the HUGO Gene Nomenclature Committee renamed several human genes, such as SEPT1 and MARCH1, partly because Excel kept converting their names into dates.

The safest default, and what the RedBison converter does, is to keep everything as strings and let your code convert specific columns explicitly. That makes the conversion lossless, which is the most important property of a data transformation.

JSON to CSV

Going the other way has its own questions:

  • Which columns? Objects in an array may not all have the same keys. The RedBison converter uses the keys of the first object as the header, so put a complete object first.
  • Nested data. CSV is flat. An object like {"user": {"name": "Ada"}} has to be flattened to a column such as user.name, or serialized as a JSON string inside a cell.
  • Arrays. A field holding a list has no natural CSV representation; you either join it or split it into multiple rows.

For flat records, conversion is straightforward. For deeply nested API responses, it is usually better to pick the fields you need first, for example with a JSONPath query in the JSON Formatter.

CSV injection

One security note for anyone exporting user data to CSV. If a cell starts with =, +, - or @, spreadsheet applications may treat it as a formula when the file is opened. A malicious value like =HYPERLINK(...) can then run in the context of whoever opens the export. OWASP recommends prefixing such cells with a single quote or otherwise neutralizing them when generating CSV from untrusted input.

A practical checklist

Before trusting a CSV conversion, check:

  1. Row counts match. If the input has 1,000 data lines and the output has 1,012 objects, line breaks inside fields have split records.
  2. Every object has the same keys.
  3. The first key is clean, with no BOM.
  4. Leading zeros survived in codes and IDs.
  5. Spot-check a row with punctuation in it.

Related tools

Launch CSV to JSON