JSON to CSV and Back: A Developer's Guide to Data Conversion

Every developer works with data in multiple formats. You might receive an API response in JSON, need to analyze it in a spreadsheet (CSV), write configuration in YAML, or exchange data with a legacy system using XML. Knowing when to use each format and how to convert between them saves hours of frustration.

When to Use Each Format

JSON (JavaScript Object Notation)

JSON is the default format for web APIs, configuration files, and data interchange between services. Its strengths are native support for nested structures, arrays, and typed values (strings, numbers, booleans, null). Use JSON when you need to represent hierarchical data or when working with web applications.

CSV (Comma-Separated Values)

CSV is the simplest tabular data format. Every row is a record and every column is a field, separated by commas. It is universally supported by spreadsheet software (Excel, Google Sheets), databases, and data analysis tools. Use CSV when your data is flat (no nesting) and you need to open it in a spreadsheet or import it into a database.

YAML (YAML Ain't Markup Language)

YAML is a human-readable data format commonly used for configuration files (Kubernetes manifests, CI/CD pipelines, Ansible playbooks). It supports the same data structures as JSON but uses indentation instead of braces, making it easier to read and edit by hand. Use YAML when humans are the primary readers and editors.

XML (eXtensible Markup Language)

XML is a verbose but powerful format that supports namespaces, attributes, schemas, and validation. While less popular for new projects, it remains essential for SOAP APIs, RSS feeds, SVG files, and enterprise integrations. Use XML when interacting with systems that require it.

How Nested JSON Maps to Flat CSV

The most common challenge in JSON-to-CSV conversion is handling nested data. CSV is inherently flat — it has rows and columns, with no concept of nesting. There are several strategies:

• Dot notation flattening — a nested object like {"address": {"city": "NYC"}} becomes a column named address.city with the value "NYC". This preserves the hierarchy in the column name.
• JSON stringification — the nested value is converted to a JSON string and stored in a single column. Useful when the nested data varies between records.
• Separate tables — for arrays of objects (like a list of orders with line items), the best approach is often to create separate CSV files for each level of nesting, linked by an ID field.

Handling Arrays and Null Values

Arrays present a unique challenge. If a JSON record contains {"tags": ["js", "react", "next"]}, how should that appear in CSV? Common approaches include:

• Joining with a delimiter — store as "js|react|next" in a single column. Simple but loses structure.
• Separate columns — create columns tag_1, tag_2, tag_3. Works when the array has a known maximum length.
• Separate rows — create one row per array element. Duplicates the parent data but preserves the full structure.

Null values are simpler but still require a decision. Most converters represent JSON null as an empty cell in CSV. When converting back, empty cells may become empty strings rather than null, so be aware of this distinction if your downstream code differentiates between them.

Common Pitfalls

• Encoding issues. CSV files don't have a built-in encoding declaration. Always use UTF-8 and be aware that Excel sometimes expects a BOM (Byte Order Mark) for proper Unicode display.
• Comma-containing values. If a field contains commas, it must be quoted. Most libraries handle this automatically, but manual string concatenation often gets it wrong.
• Newlines in fields. A CSV field with embedded newlines must be enclosed in double quotes. Failing to handle this breaks the row structure.
• Type loss. CSV has no type system. The number 42, the string "42", and the boolean true all look the same in CSV. When round-tripping data (JSON to CSV and back), you may need explicit type mapping.
• Large file memory. Parsing a 500MB JSON file into memory can crash your application. For large files, use streaming parsers or process the data in chunks.

Use Cases for Each Format

• API data to spreadsheet — JSON to CSV. Pull API responses and analyze them in Excel or Google Sheets.
• Spreadsheet data to API — CSV to JSON. Upload bulk data from a spreadsheet to a web service.
• Configuration migration — JSON to YAML or vice versa. Move configuration between tools that prefer different formats.
• Legacy integration — JSON to XML. Communicate with SOAP services or enterprise systems that require XML.