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.

By · · Updated · 8 min read

Illustration for the CSV to JSON Converter guide

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:

How a quote-aware CSV parser reads one row: a comma inside quotes stays part of the value and a doubled quote becomes one literal quote, while a naive split on commas produces too many fields
How a quote-aware CSV parser reads one row: a comma inside quotes stays part of the value and a doubled quote becomes one literal quote, while a naive split on commas produces too many fields
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 uses Papa Parse, so it handles the quoted commas, escaped quotes and embedded newlines in the example above. Values remain strings on import, so identifiers such as 007 are not accidentally changed to numbers. On export, zero and false remain present rather than turning into empty cells.

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

Frequently asked questions

What is RFC 4180?

RFC 4180, published in 2005, is the closest thing CSV has to a standard, though it is informational and documents common practice. Each record is on its own line ending in CRLF, an optional header line comes first, fields are separated by commas, fields containing commas, quotes or line breaks are enclosed in double quotes, and an embedded double quote is doubled.

How do you escape a double quote inside a CSV field?

In CSV, a double quote inside a field is escaped by doubling it, and the whole field must be enclosed in double quotes. So the value Said "hello" is written as "Said ""hello""". Fields containing commas or line breaks also need enclosing quotes, which is why splitting CSV on commas and newlines gets such rows wrong.

Why is my first CSV column name wrong after converting from Excel?

Excel's CSV UTF-8 export adds a byte order mark, three invisible bytes, at the start of the file, so the first header can come out as id instead of id. Code that looks up row.id then gets undefined. If only the first column misbehaves after a CSV to JSON conversion, the byte order mark is the likely cause.

What is CSV injection?

CSV injection happens when a cell in an exported CSV starts with =, +, - or @, and a spreadsheet application treats it as a formula when the file is opened. A malicious value such as =HYPERLINK(...) can then run in the context of whoever opens the export. OWASP recommends prefixing such cells with a single quote when generating CSV from untrusted input.

How can I check that a CSV to JSON conversion worked?

Check that row counts match, since extra objects mean line breaks inside fields split records, and that every object has the same keys. Confirm the first key has no byte order mark, that leading zeros survived in codes and IDs, and spot-check a row containing punctuation such as commas or quotes.

Sources and further reading

Open the CSV to JSON Converter

More Data Conversion

Every tool in the Data Conversion section, free and in your browser.