CSV vs JSON: the short answer

Choose CSV when each record fits a flat row and the next person or system needs a spreadsheet-style table. Choose JSON when an application needs nested objects, arrays, or explicit values such as strings, numbers, booleans, and null. Neither format is universally better: use the format the receiving system expects, then make conversion rules explicit so the data keeps its meaning.[1][2]

What each format can represent

CSV: rows and fields

CSV stores records as rows of delimited fields. A first row may contain column names, but whether it does is a choice that must be communicated to the reader. Fields containing commas, quotes, or line breaks need quoting; a quote inside a quoted field is doubled. RFC 4180 documents a common CSV format, but it is an Informational RFC and notes that implementations differ, so confirm the delimiter, quote, header, and line-ending conventions used by your importer and exporter.[1]

CSV is a text format, not a schema for application values.[1] A cell containing 42, true, or an empty field does not by itself tell every importing program whether the value is an integer, a boolean, an empty string, or a missing value.[1][2] Those meanings come from an agreed convention or from the software importing the file.[1]

JSON: values and nested structure

JSON can represent strings, numbers, booleans, null, objects, and arrays.[2] Objects hold named values, and arrays hold ordered values; either can contain further objects or arrays.[2] This makes JSON a natural fit when records have nested fields or different value types.[2]

Valid JSON syntax does not, on its own, define which fields an application requires or what a value means.[2] For an API or data exchange, document the contract and validate the payload; JSON Schema can describe data types and constraints for JSON documents.[3]

A conversion example: keep identifiers as text

Here is a small CSV file:

id,postal_code,name
017,02139,"Doe, Jane"

The quotes keep the comma in Doe, Jane inside one CSV field.[1] They do not declare a data type.[1] If 017 and 02139 are identifiers, a sensible JSON mapping keeps them as strings so their leading zeroes remain meaningful.[1][2]

{
  "id": "017",
  "postal_code": "02139",
  "name": "Doe, Jane"
}

The output is not determined by the CSV alone.[1][2] A converter could instead infer numbers, but that would change the identifiers.[1][2] Decide which columns are text, numbers, dates, or other values before converting, and check the result in the software that will consume it.[1][2]

The reverse direction needs a policy too. A JSON object such as {"profile":{"name":"Ada"},"roles":["editor","reviewer"]} has nesting and an array that a plain row of CSV fields cannot express directly.[2] You could flatten the name to a profile.name column, serialize the array into one cell, or export related tables.[1][2] Each is a design choice; document how to reverse it if round-trip conversion matters.[2]

Before converting CSV to JSON or JSON to CSV

For CSV to JSON, decide:

  • Whether the first row is a header, and what to do with blank or duplicate column names.[1]
  • Which columns remain strings and which may be parsed as numbers, booleans, or dates.[1][2]
  • How empty fields differ from null, omitted properties, and empty strings.[1][2]
  • Which delimiter, quote and escape rules, character encoding, and line endings the file uses.[1]
  • Whether a value such as editor|reviewer is one string or a list—and what escaping rule applies if the delimiter appears inside a value.[1][2]

For JSON to CSV, decide:

  • Which object properties become columns and what happens when a record omits one.[1][2]
  • How nested objects and arrays are flattened, serialized, or moved into separate tables.[1][2]
  • How null differs from an empty string in the target application.[1][2]
  • Whether every row has the same columns, and how extra or duplicate keys are handled.[1][2]

After conversion, compare record counts and representative values, including empty fields, quoted commas, line breaks, non-ASCII text, leading-zero identifiers, and nested or optional data.[1][2] A syntactically valid output can still have the wrong meaning if the mapping rules were guessed.[1][2]

A CSV security note for spreadsheet exports

When exporting untrusted values for people to open in spreadsheet software, consider CSV or formula injection: spreadsheet applications may interpret some cell values as formulas.[4] Quoting and escaping are necessary for CSV syntax but are not a universal security fix across spreadsheet applications.[1][4] Test the export in the actual spreadsheet workflow and use security guidance appropriate to that application and data flow.[4]

Quick choice guide

  • Use CSV for a flat table when people need to inspect it in a spreadsheet or a receiving system requires a delimited file.[1]
  • Use JSON when software needs nested records, arrays, or explicit primitive values.[2]
  • If you need to exchange the same data in both formats, define the mapping first and keep the original structured data when losing hierarchy would be costly.[1][2]

Frequently asked questions

Does CSV support commas inside a value?

Yes. Put a field containing a comma inside double quotes.[1] If the field also contains a double quote, write that quote twice; quoted fields can also contain line breaks under the common RFC 4180 format.[1]

Does CSV preserve numbers, dates, and leading zeroes?

CSV does not provide a portable type declaration for each column, so the importing application decides how to interpret a cell.[1][2] Keep identifiers such as postcodes or account numbers as text when their leading zeroes matter, and verify how dates and empty fields are imported.[1][2]

Can nested JSON be converted to CSV without losing information?

Not automatically. A flat CSV row has no built-in representation for nested objects or arrays.[1][2] Choose a reversible flattening or serialization convention—or use separate related tables.[1][2] If no documented mapping can recreate the original structure, the conversion is lossy.[1][2]

Is JSON always the right format for an API?

No. Follow the API's documented request and response formats. When an API uses JSON, a JSON Schema can describe and validate the expected fields, types, and constraints.[3]

How can I tell whether a conversion preserved the data?

Check the record count and compare values that are easy to change accidentally: quoted commas, line breaks, empty versus missing values, identifiers with leading zeroes, booleans, and nested data.[1][2] Also test the output with the actual program that will read it; parsing successfully is not proof that the meaning was preserved.[1][2]

Sources

[1] https://www.rfc-editor.org/rfc/rfc4180.html — RFC 4180 — Common Format and MIME Type for CSV Files [2] https://www.rfc-editor.org/rfc/rfc8259.html — RFC 8259 — The JavaScript Object Notation (JSON) Data Interchange Format [3] https://json-schema.org/learn/getting-started-step-by-step — JSON Schema — Creating Your First Schema [4] https://wstg.owasp.org/latest/4-Web_Application_Security_Testing/07-Injection/21-CSV_Injection — OWASP Web Security Testing Guide — CSV Injection