CSV, JSON, and XML: Choosing the Right Data Format and Converting Between Them

Nobody actually reads CSV with their eyes. But the moment something breaks in your data pipeline, you'll wish you had. These three formats — CSV, JSON, and XML — are everywhere: APIs return JSON, spreadsheets export CSV, and enterprise integrations speak XML. The mismatch between what one system produces and what another consumes is a constant source of friction. The differences between them are more subtle than they look.

CSV can't represent nested data. JSON has no date type and no comments (which is why JSONC and JSON5 exist). XML's verbosity makes files 2-3x larger than equivalent JSON. Choosing wrong — or converting poorly — causes data loss, type errors, and bugs that only surface in edge cases.

CSV: The Universal Tabular Format

CSV (Comma-Separated Values) is deceptively simple: rows of values separated by commas, with an optional header row. Every spreadsheet application, database, and programming language can read and write it. You can open it in a text editor. For flat tabular data, it's genuinely the right choice.

The limitations hit fast once you go beyond the simple case. RFC 4180 defines the spec, but leaves enough ambiguous that different implementations — Excel, Python's csv module, PostgreSQL's COPY — handle quoting and encoding differently. The classic trap: a field containing a comma must be quoted, a field containing a quote must escape it by doubling, and empty fields produce empty strings, not null. These cover most real-world CSV parsing bugs.

• Best for: Flat tabular data with consistent columns — database exports, spreadsheet data, simple logs
• Not suitable for: Nested structures, mixed-type data, configuration files, or any data with parent-child relationships
• Edge cases: Fields containing commas must be quoted. Fields containing quotes must escape them by doubling (""). Newlines within fields require quoting. Empty fields produce empty strings, not null.

JSON: the one you'll use most

JSON supports six data types (string, number, boolean, null, array, object), arbitrary nesting depth, and is natively parseable in every modern language. Its syntax maps directly to the data structures you work with every day. It's the default for REST APIs, config files, and NoSQL databases like MongoDB and Firestore.

JSON's main limitations are the lack of comments (making it awkward for human-edited config files, which is why JSONC and JSON5 exist), no date type (dates are typically ISO 8601 strings), no binary data support (binary must be Base64-encoded as strings), and no schema enforcement (any key can hold any type, which JSON Schema addresses).

{
  "employees": [
    {
      "id": 1,
      "name": "Alice Chen",
      "department": "Engineering",
      "skills": ["Go", "Kubernetes", "PostgreSQL"],
      "active": true
    },
    {
      "id": 2,
      "name": "Bob Park",
      "department": "Design",
      "skills": ["Figma", "CSS", "Motion"],
      "active": false
    }
  ],
  "total": 2,
  "page": 1
}

XML: still running half the enterprise world

XML (Extensible Markup Language) predates JSON and was the dominant data interchange format from the late 1990s through the mid-2010s. It remains deeply embedded in enterprise software (SOAP APIs, SAML authentication, Spring configuration), document formats (DOCX, SVG, RSS, XHTML), and regulated industries (healthcare HL7, finance FIX/FpML) where formal schemas and validation are required.

XML's key advantage over JSON is its mature ecosystem: XML Schema (XSD) provides rigorous type and structure validation, XSLT enables powerful document transformation, XPath provides a query language for navigating documents, and namespaces prevent name collisions when combining data from multiple sources. These features matter in enterprise contexts where contracts between systems must be formally specified and validated.

XML's key disadvantage is verbosity. Every piece of data requires an opening and closing tag, making XML documents 2-3x larger than equivalent JSON for the same data. The distinction between attributes and child elements introduces ambiguity with no clear convention, and parsing XML is significantly more complex and slower than parsing JSON.

Where you'll actually need conversions

JSON to CSV

JSON-to-CSV conversion is straightforward when the JSON is a flat array of objects with consistent keys. Each object becomes a row; each unique key becomes a column header. The challenge arises with nested data: a JSON field containing an object or array has no natural CSV representation. Common approaches include serializing nested values as JSON strings within the CSV cell, flattening nested objects using dot notation (user.address.city becomes a column named 'user.address.city'), or ignoring nested fields entirely.

CSV to JSON

CSV-to-JSON conversion requires deciding how to handle data types. Raw CSV has no type information — the string '42' could be a number, a zip code (string), or a boolean-like flag. Intelligent converters apply type coercion: strings that look like numbers become JSON numbers, 'true'/'false' become booleans, and empty cells become null. This heuristic is usually correct but can misfire — zip codes starting with 0 (like '07052') lose their leading zero when converted to numbers.

XML to JSON

XML-to-JSON conversion is inherently lossy because XML has concepts that JSON does not: attributes versus child elements, processing instructions, comments, and mixed content (text interleaved with elements). The standard convention is to place attributes under a special '@attributes' key and group repeated sibling elements into arrays. Text content of elements with attributes goes under a '#text' key.

<!-- XML -->
<book id="101" lang="en">
  <title>Clean Code</title>
  <authors>
    <author>Robert C. Martin</author>
  </authors>
</book>

// JSON equivalent
{
  "book": {
    "@attributes": { "id": "101", "lang": "en" },
    "title": "Clean Code",
    "authors": {
      "author": "Robert C. Martin"
    }
  }
}

The short decision guide

• Use CSV when: exchanging flat tabular data with spreadsheet users, importing/exporting database tables, processing large datasets where compactness matters, or when the consumer expects rows and columns
• Use JSON when: building web APIs, storing configuration files, working with JavaScript/TypeScript applications, exchanging nested or hierarchical data, or communicating with NoSQL databases
• Use XML when: integrating with enterprise SOAP services, working with SVG or RSS feeds, operating in regulated industries that mandate XML schemas, or when formal validation with XSD is required
• Use Protocol Buffers or MessagePack when: performance and bandwidth are critical (binary formats are 3-10x smaller and faster to parse than JSON/XML)

The traps that actually bite people

• CSV encoding: Always specify UTF-8 encoding explicitly. Excel defaults to the system locale encoding (often Windows-1252), causing garbled characters for international text.
• JSON number precision: JavaScript cannot safely represent integers larger than 2^53 - 1 (Number.MAX_SAFE_INTEGER). Database IDs from 64-bit systems (like Twitter/X snowflake IDs) must be transmitted as strings to avoid silent truncation.
• XML namespace conflicts: When combining XML from multiple sources, unqualified element names may collide. Always use namespace-aware parsers and declare namespaces explicitly.
• CSV delimiter ambiguity: Some locales use semicolons instead of commas as the CSV delimiter (because commas are used as decimal separators). TSV (tab-separated) avoids this ambiguity entirely.