JSON to CSV and XML: A Data Conversion Guide

Data formats should not matter. You have information, you need to move it between systems, and the format is just the container. In practice, format mismatches are a common friction point in any data workflow.

Your API returns JSON. Your client wants a CSV they can open in Excel. Your legacy system only accepts XML. Your database export tool produces TSV. Every system boundary adds a conversion step.

The conversions are rarely hard, but they are fiddly. JSON's nested objects do not map cleanly to CSV's flat rows. CSV has no data types, so everything becomes a string. XML's verbose syntax doubles the file size. Knowing these trade-offs helps you pick the right format and convert cleanly.

The JSON to CSV and CSV to JSON converters handle the common conversions instantly. Paste your data in one format, get it out in another.

JSON: The Modern Default

JSON (JavaScript Object Notation) is the dominant data format for web APIs, configuration files, and modern data interchange. Its popularity comes from being lightweight, human-readable, and natively supported in JavaScript.

JSON supports four data types: strings, numbers, booleans, and null. It also supports two structures: objects (key-value pairs) and arrays (ordered lists). These building blocks can be nested to any depth.

`json { "name": "Jane Smith", "age": 32, "active": true, "address": { "city": "Amsterdam", "country": "NL" }, "tags": ["developer", "blogger"] } `

Strengths: - Native in JavaScript, well-supported in every language - Supports nested structures - Preserves data types (numbers vs strings) - Compact compared to XML

Weaknesses: - No native date type (dates are stored as strings) - No comments allowed in standard JSON - No schema enforcement (unless you use JSON Schema separately) - Large files can be hard to read without formatting

The JSON Formatter helps when working with JSON. APIs often return minified JSON on a single line. Pretty-printing it with proper indentation makes the structure visible.

CSV: The Universal Spreadsheet Format

CSV (Comma-Separated Values) is the simplest data format. Each line is a row, columns are separated by commas, and the first row is usually headers.

` name,age,city,country Jane Smith,32,Amsterdam,NL John Doe,28,Berlin,DE `

Strengths: - Opens in Excel, Google Sheets, and every data tool - Extremely compact (minimal overhead per record) - Easy to generate and parse - Every programming language has CSV libraries - Non-technical users can read and edit it

Weaknesses: - No data types (everything is a string) - No nested structures (flat rows only) - Delimiter conflicts (what if a value contains a comma?) - No standard for encoding, quoting, or escaping - Multiple rows of headers or metadata are not standardized

The delimiter problem is the biggest practical issue. If a field value contains a comma, the field must be quoted: "Smith, Jane". If it contains a quote, the quote must be doubled: "He said ""hello""". These rules are defined in RFC 4180, but not every tool follows them.

Some systems use semicolons instead of commas (common in European locales where the comma is the decimal separator). Others use tabs (TSV). When a CSV does not parse correctly, the delimiter is usually the first thing to check.

XML: Still Alive in Enterprise Systems

XML (Extensible Markup Language) was the dominant data interchange format before JSON took over. It remains widely used in enterprise systems, government data feeds, SOAP web services, RSS feeds, and configuration files (like Maven's pom.xml and Android layouts).

`xml Jane Smith 32

Strengths: - Schema validation (XSD enforces structure and data types) - Namespace support (multiple vocabularies in one document) - Mature tooling (XSLT, XPath, XQuery) - Self-describing (tags explain what the data means) - Required by many government and enterprise standards

Weaknesses: - Verbose (the same data is 2-3x larger than JSON) - Harder to parse in JavaScript (requires DOM parsing) - Attributes vs elements ambiguity (is the data in Jane or ?) - Declining ecosystem (fewer new tools, fewer developers experienced with it)

If you are working with a system that provides XML and you need JSON, the XML to JSON converter handles the structural mapping. Arrays are the tricky part: XML has no native array concept, so converters must infer arrays from repeated elements.

The Tricky Parts of Converting Between Formats

JSON to CSV: flattening nested data. JSON supports nesting. CSV does not. When converting, nested objects need to be flattened into dot-notation columns:

` name,age,address.city,address.country Jane Smith,32,Amsterdam,NL `

Arrays are harder. If a JSON record has tags: ["developer", "blogger"], you have several options: join them into a single cell (developer|blogger), create separate columns (tag_1, tag_2), or create separate rows (one per tag, duplicating the other fields). There is no universally right answer. The JSON to CSV converter handles common cases and lets you control the flattening approach.

CSV to JSON: inferring types. CSV stores everything as text. When converting to JSON, you need to decide: is "32" the number 32 or the string "32"? Is "true" a boolean or a string? Most converters infer types by pattern (numbers look like numbers, "true"/"false" become booleans), but edge cases exist. A US zip code like "07101" should stay a string (leading zero matters), but a naive converter might turn it into the number 7101.

XML to JSON: arrays and attributes. XML uses repeated elements for what would be arrays in JSON. A converter detects that three elements inside should become an array by spotting repeated sibling elements with the same name. XML attributes () have no direct JSON equivalent. Converters typically create a special key like @id or _attributes.

Encoding issues. CSV files can be encoded in UTF-8, Latin-1, Windows-1252, or other character sets. If you see garbled characters (mojibake) after conversion, the encoding is wrong. UTF-8 is the safest default. Save and open files as UTF-8 to avoid issues.

When to Use Which Format

Use JSON when: - Building or consuming web APIs - Storing configuration files for applications - Passing data between JavaScript-based systems - You need nested structures - You want to preserve data types

Use CSV when: - Sharing data with non-technical users who will open it in Excel - Importing or exporting data from databases - Working with flat, tabular data (no nesting needed) - File size matters (CSV is the most compact for tabular data) - The data is simple enough that a spreadsheet is the right tool

Use XML when: - Integrating with enterprise or government systems that require it - You need schema validation (XSD) - Working with document-oriented data (HTML-like content with mixed text and structure) - Using tools that expect XML (RSS feeds, SOAP services, Android development)

For new projects, JSON is almost always the right choice for APIs and data interchange. CSV for tabular exports and imports. XML only when required by an external system or standard.

The JSON Formatter is useful regardless of which format you start with. If you convert from CSV or XML to JSON, run the result through the formatter to verify the structure looks correct before using it in your application.

FAQ

Can I convert JSON with deeply nested objects to CSV?

Yes, but the result may be impractical. If your JSON has 5 levels of nesting, the CSV will have column names like level1.level2.level3.level4.level5.value, which is hard to work with in a spreadsheet. For deeply nested data, consider flattening only to the level that makes sense for your use case, or using a database instead of CSV.

Why does my CSV look wrong when I open it in Excel?

Excel makes several assumptions that may not match your file. It auto-detects delimiters (sometimes incorrectly), interprets numbers (removing leading zeros, converting long numbers to scientific notation), and uses the system locale for date parsing. To control this, use Excel's "Import from Text" feature instead of double-clicking the file, which lets you specify the delimiter, encoding, and column data types.

Is YAML a replacement for JSON?

YAML is a superset of JSON that adds human-friendly features: indentation-based structure, comments, multi-line strings, and anchors for reuse. It is popular for configuration files (Docker Compose, Kubernetes, GitHub Actions) but less common for data interchange. YAML is more readable for humans but more error-prone (indentation sensitivity) and slower to parse than JSON.

How do I handle CSV files with millions of rows?

Browser-based converters have memory limits. For large files (over 100MB), use command-line tools. Python's pandas library reads and converts CSV files efficiently with pd.read_csv() and to_json(). For streaming conversions without loading the entire file into memory, use Python's built-in csv module or jq for JSON processing.