How to Convert JSON to CSV: Nested Data Guide

The Core Challenge: Hierarchical Trees vs. Tabular Grids

JavaScript Object Notation (JSON) is intrinsically a tree-like data structure. It allows arbitrary depth, where objects can hold child objects, arrays, and primitive values. In contrast, Comma-Separated Values (CSV) is strictly a two-dimensional grid consisting of columns (fields) and rows (records).

To successfully map a JSON tree into a CSV grid, the converter must solve two core architectural challenges: object flattening and RFC 4180 character escaping.

Mastering Dot Notation for Nested Objects

When an API returns nested child objects, standard industry convention utilizes dot notation to preserve relational lineage within flat spreadsheet column headers. For example, consider an API returning order information with shipping details:

// Original Hierarchical JSON:
{
  "orderId": "ORD-5842",
  "customer": {
    "name": "Marcus Aurelius",
    "shipping": {
      "city": "Rome",
      "postalCode": "00118"
    }
  },
  "total": 149.50
}

By recursively walking child keys, the converter translates this nested object into four flat columns:

orderId,customer.name,customer.shipping.city,customer.shipping.postalCode,total
ORD-5842,"Marcus Aurelius",Rome,00118,149.50

When opened in Microsoft Excel or Google Sheets, each nested property occupies its own distinct column, enabling sorting, filtering, and pivot-table aggregation.

RFC 4180 Escaping Rules: Preventing Broken Spreadsheets

Many homemade conversion scripts fail when text values contain punctuation. Under the international RFC 4180 standard, specific escaping rules must be enforced:

• Delimiters Inside Values: If a text string contains a comma (e.g. "Austin, TX") or selected delimiter, the entire string must be enclosed in quotes: "Austin, TX". Without quotes, spreadsheet programs treat the comma as a new column, shifting all subsequent fields to the right.
• Double Quotes Inside Values: If a field contains quotes (e.g. 15" Screen), the internal quotation mark must be escaped by prefixing it with an additional quotation mark: "15"" Screen".
• Line Breaks Inside Values: Multi-line descriptions and address strings must be enclosed in quotes so that spreadsheet engines treat the newline as part of the cell rather than the start of a new row.

Handling Missing or Inconsistent Keys Across Records

Because JSON is schema-less, different records in the same array often have missing or optional keys. A robust converter must perform a two-pass scan:

1. Header Collection Pass: Iterate across all objects in the input array to construct a global set of all unique property keys.
2. Row Generation Pass: Iterate across each record and emit a value for every global header key. If a record lacks a specific property, an empty string ("") is output to preserve alignment.

Delimiters: Comma vs Semicolon vs Tab

Frequently Asked Questions