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 Redbison Dev · · Updated · 8 min read

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:
- Each record is on its own line, ending in CRLF.
- An optional header line has the same format as records.
- Fields are separated by commas, and each line should have the same number of fields.
- Fields containing commas, double quotes or line breaks must be enclosed in double quotes.
- 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 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
| Variation | Where it comes from |
|---|---|
| Semicolon separator | Excel in locales where the comma is the decimal separator (much of Europe) |
| Tab separator (TSV) | Database exports, copying cells from spreadsheets |
| LF instead of CRLF | Unix tools and most programming languages |
| UTF-8 BOM at start | Excel's "CSV UTF-8" export |
| No header row | Many 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
007become the number7? Not if it is a product code or a ZIP code. - Should
1e5become100000? Probably not if it is a part number. - Should an empty cell be
"",null, or a missing key? - Should
TRUE,yesand1all becometrue? - Large IDs like
9007199254740993cannot 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 asuser.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:
- 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.
- Every object has the same keys.
- The first key is clean, with no BOM.
- Leading zeros survived in codes and IDs.
- Spot-check a row with punctuation in it.
Related tools
- JSON Formatter to inspect and query the converted JSON
- JSON to YAML Converter when the destination is a config file
- Data Format Comparison to see how JSON compares with other formats
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
- RFC 4180: Common Format and MIME Type for Comma-Separated Values (CSV) Files, IETF. The quoting rules for commas, line breaks and doubled quotes.
- RFC 8259: The JavaScript Object Notation (JSON) Data Interchange Format, IETF. The JSON format the conversion produces.
- csv: CSV File Reading and Writing, Python Software Foundation. How real-world CSV dialects differ in delimiters and quoting.
- CSV Injection, OWASP Foundation. Why exported cells starting with = + - or @ are risky.
Open the CSV to JSON Converter
More Data Conversion
Every tool in the Data Conversion section, free and in your browser.
- JSON Formatter
- JSON to TypeScript Generator
- Binary to Text Converter
- XML to JSON Converter
- Markdown to HTML Converter
- HTML to Markdown Converter
- CSV to JSON
- JSON to YAML
- Excel to JSON Converter
- Data Visualization Tool
- JSON Schema Validator & Generator
- TOML Converter
- cURL to Code Converter
- Markdown Table Generator