How to Convert JSON to CSV Table: Flattening Guide

To convert a JSON array or object to a CSV table, parse the key-value structures, flatten nested objects into dot-notation column headers (such as user.address.city), normalize heterogeneous array schemas across all records, escape reserved characters according to IETF RFC 4180, and serialize the tabular matrix into comma-separated rows.

JavaScript Object Notation (JSON) is the standard format for web APIs and document databases due to its flexible, tree-like structure. However, data analysts require flat two-dimensional tables to import records into applications like Microsoft Excel, Google Sheets, or SQL databases. Converting hierarchical JSON payloads into CSV grids bridges the gap between nested developer data structures and analytical workflows.

Transforming JSON documents into rectangular CSV tables requires unwinding nested objects, normalizing asymmetric schemas, serializing complex types like arrays and booleans, and ensuring compliance with character encoding standards. This technical guide explains JSON parsing, recursive flattening algorithms, edge-case handling, and CSV serialization.

Key Definitions: JSON Format (RFC 8259), CSV Format (RFC 4180), Object Flattening, and Array Normalization

Converting JSON data to CSV requires understanding four core concepts:

• JSON Format (IETF RFC 8259): A lightweight data format defining six structural types: objects (key-value maps), arrays (ordered lists), strings, numbers, booleans (true / false), and null primitives. JSON supports arbitrary nesting depth and flexible schemas.
• CSV Format (IETF RFC 4180): A standardized format for two-dimensional tabular data. Records are separated by line breaks (CRLF, \r\n) and fields are delimited by commas (,). Fields containing commas, double quotes, or newlines must be enclosed in double quotes ("..."), with internal quotes escaped as "".
• Object Flattening: An algorithmic transformation converting a nested JSON hierarchy into a single-level key-value map. Child keys are concatenated with parent keys using dot notation (e.g., user.location.city) to generate flat header columns.
• Array Normalization: The process of inspecting all object records in a JSON array to construct a unified master schema. Because JSON allows heterogeneous object keys across records, array normalization ensures every row aligns with identical header column positions.

Structure of JSON Hierarchical Data vs. CSV 2D Grid Structure

The main challenge when converting JSON to CSV stems from structural differences: JSON represents data as an N-dimensional tree, whereas CSV represents data as a strict 2D rectangular matrix.

In JSON, relationships are expressed through nesting. Objects can contain sub-objects and lists of child objects, capturing complex one-to-many relationships:

[
  {
    "id": 101,
    "user": {
      "name": "Sarah Connor",
      "contact": { "email": "sarah@example.com", "phone": "+1-555-0199" }
    }
  }
]

Conversely, CSV tables have no concept of sub-objects or nested lists. A CSV file requires a fixed set of columns ($C_1, C_2, \dots, C_n$) and sequential rows ($R_1, R_2, \dots, R_m$). To convert tree nodes into grid cells, conversion engines linearize the structural path of every leaf node into a flat header coordinate.

Step-by-Step: How to Convert a JSON Array to a CSV Table

Convert your JSON array to a clean CSV table using these five steps with EasyExtract’s in-browser converter:

1. Supply JSON Source File or Raw Text: Copy your JSON text from your API output or database export and paste it into the JSON Table Extractor, or drag and drop your .json file into the drop zone.
2. Parse and Validate JSON Syntax: The client-side engine validates the payload against RFC 8259 rules, checking bracket balancing, quoted strings, and token integrity before creating JavaScript object memory structures.
3. Execute Union Schema Discovery: The algorithm scans every object entry in the root array, collecting all top-level and nested property keys to build a unified master header list.
4. Perform Recursive Object Flattening: Nested child objects are unwound and intermediate key paths are joined with dot notation (e.g., customer.address.city) to create flat header columns for scalar values.
5. Serialize RFC 4180 CSV Output and Download: The flattened matrix is serialized into comma-delimited rows. Reserved characters are enclosed in double quotes, and a UTF-8 byte order mark (BOM) is attached before downloading your .csv file.

Recursive Object Flattening: Converting Nested Keys into Flat Header Columns

Recursive object flattening transforms nested JSON sub-trees into single-level dictionary records suitable for CSV column alignment.

Consider a nested JSON record containing customer details:

{
  "order_id": "ORD-9482",
  "customer": {
    "name": "Alex Mercer",
    "address": { "city": "London", "postcode": "EC1A 1BB" }
  }
}

A recursive flattening function traverses the object tree from the root node. When encountering an object, it appends key names to a path buffer and processes child properties. Reaching a primitive leaf node joins the path with dots to establish column headers:

• order_id $\rightarrow$ Value: "ORD-9482"
• customer.name $\rightarrow$ Value: "Alex Mercer"
• customer.address.city $\rightarrow$ Value: "London"
• customer.address.postcode $\rightarrow$ Value: "EC1A 1BB"

In JavaScript, this recursive logic can be implemented as follows:

function flattenObject(obj, prefix = '', result = {}) {
  for (const key in obj) {
    if (Object.prototype.hasOwnProperty.call(obj, key)) {
      const propName = prefix ? `${prefix}.${key}` : key;
      const val = obj[key];
      if (typeof val === 'object' && val !== null && !Array.isArray(val)) {
        flattenObject(val, propName, result);
      } else {
        result[propName] = val;
      }
    }
  }
  return result;
}

Unwinding nested objects into dot-notation primitives gives deep JSON attributes explicit column headers while preserving hierarchy.

Handling Special Data Types: Null Values, Booleans, Nested Arrays, and Special Characters

Converting non-string JSON primitives into text-based CSV rows requires strict formatting rules:

1. Null Primitives (null)

JSON null values represent empty data points. During CSV conversion, null primitives are serialized as empty strings between field delimiters (e.g., val1,,val3). Spreadsheet software treats missing properties as unpopulated empty cells rather than text strings containing "null".

2. Boolean Flags (true / false)

JSON booleans convert into literal string primitives ("TRUE" or "FALSE"). Applications like Microsoft Excel and Google Sheets parse these string values as native boolean states upon importing.

3. Primitive and Object Arrays

Arrays inside JSON records are processed using three standard strategies:

• Stringification (Default): Arrays of scalar primitives (e.g., ["admin", "user"]) are serialized into delimited string representations like "admin; user" within a single CSV cell.
• Indexed Column Expansion: Arrays are expanded into numbered column headers (e.g., tags.0, tags.1).
• Row Unwinding (Denormalization): For arrays of child objects, the converter creates a separate CSV row for each array entry, duplicating parent metadata across rows.

4. Reserved Special Characters and Escaping

Per RFC 4180 rules, if a cell value contains commas (,), double quotes ("), or line breaks (\n), the field is wrapped in double quotes. Internal double quotes are escaped by doubling them (e.g., "John ""Doc"" Smith").

JSON vs. CSV vs. TSV vs. XML: Data Format Comparison Table

Choosing between data formats depends on operational priorities. The table below compares JSON, CSV, TSV, and XML:

Feature	JSON (RFC 8259)	CSV (RFC 4180)	TSV (Tab-Separated)	XML (W3C Standard)
Data Model	Hierarchical Tree	2D Rectangular Grid	2D Rectangular Grid	Hierarchical Document Tree
Nested Structures	Native (Objects/Arrays)	Unsupported (Requires flattening)	Unsupported (Requires flattening)	Native (Nested Elements)
Field Delimiter	Colons (:) & Commas (,)	Comma (,)	Tab (\t)	Tags (</tag>)
Schema Flexibility	Dynamic / Heterogeneous	Rigid / Fixed Columns	Rigid / Fixed Columns	Dynamic (XSD Validation)
Spreadsheet Native	No (Requires conversion)	Direct / Native Open	Direct / Native Open	No (Requires XML map)
Payload Overhead	Moderate	Minimal	Minimal	High (Verbose markup)

While JSON is ideal for API communication, CSV and TSV offer superior storage efficiency and spreadsheet compatibility. If you only need to extract specific key attributes rather than full tables, use our JSON Field Extractor.

Importing Converted CSV Files into Microsoft Excel and Google Sheets Without Encoding Errors

Importing CSV files into spreadsheet software requires proper character encoding handling to prevent text corruption.

Preventing Character Corruption with UTF-8 Byte Order Mark (BOM)

Desktop versions of Microsoft Excel on Windows default to local ANSI character encodings. If your JSON source contains international UTF-8 characters, Excel may render them as garbled characters.

To guarantee clean UTF-8 rendering, EasyExtract attaches a 3-byte Byte Order Mark sequence (0xEF, 0xBB, 0xBF) to generated CSV files. This BOM signature instructs Excel to open the file using UTF-8 encoding immediately.

Importing CSV into Google Sheets

1. Open Google Sheets and launch a blank spreadsheet.
2. Click File > Import and choose Upload.
3. Select your converted CSV file.
4. Set Import location to “Replace spreadsheet” and Separator type to “Detect automatically”.
5. Click Import data.

Importing CSV into Microsoft Excel via Data Query Wizard

1. Open Excel, click the Data tab, and select From Text/CSV.
2. Select your converted .csv file.
3. Ensure File Origin is set to 65001 : Unicode (UTF-8) and Delimiter is set to Comma.
4. Click Load to import data.

Common Conversion Problems: Mismatched Schema Keys, Deep Recursion Limits, and Unescaped Commas

Processing real-world JSON datasets involves three common technical challenges:

1. Mismatched Schema Keys (Asymmetric JSON Arrays)

Records within JSON arrays often contain inconsistent property keys. For example, record #1 may have {"id": 1, "email": "a@test.com"} while record #2 contains {"id": 2, "phone": "+1-555-0100"}.

Converters inspecting only the first item omit non-matching keys from subsequent rows. EasyExtract executes a full union schema scan across all array elements to discover every key. Missing keys in individual objects are populated with empty strings, maintaining vertical alignment across columns.

2. Deep Recursion Limits and Memory Overflows

Heavily nested JSON structures can trigger stack overflow limits in JavaScript engines during recursive traversal. Enterprise conversion engines use iterative traversal stacks or set maximum recursion caps to process deep objects safely without browser memory crashes.

3. Unescaped Commas and Quotes in Text Fields

If JSON string values contain commas or newlines, basic line-splitting scripts split single fields across multiple columns or rows. RFC 4180 compliant serializers wrap delimited strings in double quotes and escape internal quotes correctly.

Privacy and Security: Local Browser-Side JSON Processing Without Cloud Uploads

JSON files frequently contain sensitive customer details, financial data, or API credentials. Uploading JSON files to server-side converters poses privacy risks, as remote servers can store uploaded files in temporary storage or access logs.

EasyExtract uses a zero-trust browser architecture. When using the JSON Table Extractor:

• 100% Client-Side Execution: Parsing, flattening, and CSV generation occur entirely inside your browser JavaScript engine.
• Zero Network Uploads: No JSON data or exported CSV files are transmitted to external cloud servers.
• Regulatory Compliance: In-browser processing satisfies GDPR, HIPAA, SOC 2, and CCPA privacy standards.
• Offline Operation: The tool functions offline once loaded, ensuring privacy.

Frequently Asked Questions

Related Tools and Reading

Explore related data processing tools on EasyExtract:

• JSON Table Extractor – Convert JSON arrays and nested objects into spreadsheet CSV files in your browser.
• JSON Field Extractor – Isolate specific JSON keys and values without exporting full tabular matrices.
• CSV Column Extractor – Filter, reorder, and isolate columns from CSV datasets.

Sources and References

This technical guide adheres to official data format standards and IETF specifications:

• IETF RFC 8259: The JavaScript Object Notation (JSON) Data Interchange Format. The official IETF specification defining JSON syntax, data types, and UTF-8 encoding rules. IETF RFC 8259 Standard.
• IETF RFC 4180: Common Format and MIME Type for Comma-Separated Values (CSV) Files. Standard defining CSV row formatting, line break standards, field quoting, and character escaping. IETF RFC 4180 Standard.
• ECMA-404: The JSON Data Interchange Syntax. Standardized specification of JSON syntax maintained by Ecma International. ECMA-404 Specification.