JSON vs CSV vs XML: Which Data Format to Use

An API sends JSON, the finance team wants a spreadsheet, and an older partner system only speaks XML. All three formats carry structured data as plain text, but they're built around different shapes of data. The format that fits depends on what the data looks like and who or what has to read it.

Here's how JSON, CSV and XML compare, what goes wrong when you convert between them, and how to pick one.

The same data in all three

Two orders, each with a customer and a list of items.

JSON

[
  {"id": 1001, "customer": {"name": "Ana Silva", "city": "Lisbon"}, "items": ["mug", "tea"], "total": 24.5},
  {"id": 1002, "customer": {"name": "Okafor, Mia", "city": "Lagos"}, "items": ["notebook"], "total": 12}
]

CSV

id,customer.name,customer.city,items,total
1001,Ana Silva,Lisbon,mug; tea,24.5
1002,"Okafor, Mia",Lagos,notebook,12

XML

<orders>
  <order id="1001">
    <customer><name>Ana Silva</name><city>Lisbon</city></customer>
    <items><item>mug</item><item>tea</item></items>
    <total>24.5</total>
  </order>
  <order id="1002">
    <customer><name>Okafor, Mia</name><city>Lagos</city></customer>
    <items><item>notebook</item></items>
    <total>12</total>
  </order>
</orders>

The CSV was produced by our JSON to CSV Converter with nested objects flattened into customer.name and customer.city columns and the item list joined with semicolons. Notice what had to happen to make it fit: the nesting became column names, the list became one text cell, and "Okafor, Mia" had to be quoted because it contains a comma.

JSON: nested data with basic types

JSON, defined in RFC 8259, has objects, arrays, strings, numbers, true/false and null. That small set maps directly onto the data structures of almost every programming language, which is why it became the default for web APIs and configuration.

Strengths: any depth of nesting, lists of any length, a distinction between numbers, text, booleans and null, and a very simple grammar. RFC 8259 requires UTF-8 for JSON exchanged between systems, so character encoding is rarely a problem.

Weak spots:

• No comments. Standard JSON has none, which is awkward for hand-edited config files.
• Large numbers. RFC 8259 notes that integers in the range [−(2⁵³)+1, (2⁵³)−1] are interoperable, because many parsers store numbers as 64-bit floating point. Larger IDs can silently change: JavaScript's JSON.parse reads 90071992547409931 as 90071992547409940. Send big IDs as strings.
• No dates or binary. Dates are usually ISO 8601 strings, and binary data is usually Base64 text, which adds a third to its size (see What Is Base64 Encoding?).
• Repeated keys. Every object repeats its field names, so large arrays of identical records are bulkier than CSV.

When JSON arrives as one long line, our JSON Formatter indents it, validates it and points to the exact line and column of any syntax error, without rounding large numbers.

CSV: flat tables, nothing else

CSV is a header row and then one line per record, with values separated by commas. It's the universal exchange format for spreadsheets, databases and analytics tools, and it's the most compact of the three for tabular data because column names appear only once.

But CSV is barely a standard. RFC 4180 documents the common format, but it's an informational memo that "does not specify an Internet standard of any kind." Its main rules: fields containing commas, double quotes or line breaks should be enclosed in double quotes, and a double quote inside a field is written twice (""). In practice, files vary:

• Delimiters: commas, semicolons (common in countries that use a decimal comma), tabs or pipes.
• Encodings: UTF-8, UTF-8 with a byte-order mark, or a legacy encoding from an older export.
• Line endings: the RFC specifies CRLF, but many tools write just LF.

The biggest limitation: CSV has no types. Every value is text, and it's up to the reader to guess. That's where the classic spreadsheet problems come from: ZIP codes like 02134 losing their leading zero, long numbers turned into scientific notation, and product codes that look like dates being converted into dates. When our CSV to JSON Converter detects types, it keeps 02134 as the string "02134" rather than the number 2134 for exactly this reason.

CSV also can't represent nesting. A list of items per order has to be joined into one cell, spread across numbered columns, or moved to a second file with one row per item.

XML: documents, attributes and schemas

XML, specified by the W3C in XML 1.0, is a markup language. Data lives in nested elements, and elements can carry attributes, like id="1001" above. It predates JSON as the main data exchange format and is still standard in many areas: office documents (DOCX and XLSX are zipped XML), RSS feeds, sitemaps, SVG images, and much of enterprise, banking and government data exchange.

Strengths:

• Mixed content. Text with markup inside it, like a paragraph containing bold words, is natural in XML and awkward in JSON.
• Schemas and namespaces. XML Schema can define exactly which elements, types and values are allowed, and namespaces let vocabularies from different sources coexist in one document.
• Comments and processing instructions are part of the format.

Weak spots: it's verbose, since every element name appears twice, and the choice between an attribute and a child element is a design decision that nothing enforces. Like CSV, plain XML has no data types without a schema: <total>24.5</total> is text until something says otherwise. For a messy XML file, our XML Formatter indents it and reports well-formedness errors.

Side by side

What to watch for when converting

JSON to CSV

• Nested objects become dotted column names like customer.city. Deep nesting produces many columns.
• Arrays don't fit in a cell. Join simple lists with a separator, keep them as JSON text in the cell, or split them into a second table.
• Missing fields become empty cells, and the column set is the union of all keys across records.
• Types are lost. true, null and the number 12 all become plain text.

CSV to JSON

• Decide on types deliberately. Turning "42" into a number is usually right; turning "02134" into one is not.
• Check the delimiter and encoding before converting. A semicolon file read as comma-separated becomes one giant column.
• Empty cells could mean an empty string, null or a missing field. Pick one rule.

To or from XML

• Attributes vs. elements have no JSON equivalent, so converters use conventions like an @ prefix for attributes.
• One-item lists are a classic trap: <items><item>notebook</item></items> can turn into a single value instead of an array, and code that expects a list breaks.

Which one should you use?

• Building or consuming a web API: JSON. It's what clients and frameworks expect.
• Handing data to people who use spreadsheets, or bulk importing into a database: CSV, with UTF-8 encoding and a clear header row.
• Data with real nesting (orders with items, users with addresses): JSON, or two related CSV files if it must open in a spreadsheet.
• Documents, feeds or an industry standard that requires it: XML. Don't fight a schema your partners already use.
• Configuration files edited by hand: JSON works, but formats that allow comments are friendlier. If you use JSON, keep it formatted and validated.

FAQ

Is JSON replacing XML?

For web APIs, largely yes. XML remains standard for documents, feeds, sitemaps and many enterprise and government systems, and it isn't going away.

Why does Excel mangle my CSV file?

Because CSV has no types, Excel guesses them when you open a file directly. Use Excel's import from text/CSV, set columns like ZIP codes and IDs to Text, and the values stay as written.

Which format is smallest?

For flat, tabular data, CSV, because field names appear only once. Once compressed with gzip, the differences shrink considerably.

Can CSV hold line breaks inside a value?

Yes, if the value is enclosed in double quotes, as RFC 4180 describes. Some simple parsers still handle it badly, so test with your actual tools.

• #CSV
• #Data formats
• #Excel
• #JSON
• #XML