DEV Community

Rasika Dangamuwa
Rasika Dangamuwa

Posted on

Why JSON-to-CSV Conversion Fails in Production: 5 Flattening Traps and Edge Cases

#webdev #programming #productivity #javascript

Every developer has written a JSON-to-CSV export. Whether pulling records from a third-party API, exporting MongoDB collections for business analysts, or dumping event streams into spreadsheets, converting JSON objects into tabular rows looks deceptively simple.

At first glance, it feels like a one-liner: iterate over an array of objects, extract the keys for headers, and join values with commas. But in production, naive converters consistently corrupt data, misalign columns, or generate spreadsheets that break downstream ingestion pipelines.

Here are the five critical edge cases where JSON-to-CSV conversion fails—and how to handle each one reliably.

1. RFC 4180 Escaping: Unescaped Quotes, Commas, and Line Breaks

The most frequent bug in custom CSV exporters is violating RFC 4180 escaping rules. In standard JSON, a multi-line string or an address containing commas is completely valid:

[
  {
    "id": 101,
    "name": "Jane Doe",
    "notes": "First line.\nSecond line with \"quoted terms\" and, of course, commas."
  }
]
Enter fullscreen mode Exit fullscreen mode

Concatenating values with commas (Object.values(row).join(",")) turns this single record into three broken rows, offsetting column counts across the entire spreadsheet.

The Fix: RFC 4180 mandates that any field containing a comma, newline (\n or \r\n), or double quote (") must be wrapped in double quotes. Internal double quotes must be doubled (""):

function escapeCSVField(val) {
  if (val === null || val === undefined) return "";
  const str = String(val);
  if (str.includes(",") || str.includes("\n") || str.includes("\r") || str.includes('"')) {
    return `"${str.replace(/"/g, '""')}"`;
  }
  return str;
}
Enter fullscreen mode Exit fullscreen mode

2. Schema Sparsity and Key Drift Across Records

Unlike relational databases where every record adheres to fixed table columns, JSON collections are typically sparse or polymorphic:

[
  { "id": 1, "name": "Alice", "role": "admin" },
  { "id": 2, "name": "Bob", "department": "Security" },
  { "id": 3, "name": "Charlie", "role": "editor", "tags": "writer" }
]
Enter fullscreen mode Exit fullscreen mode

If you determine CSV headers solely from the first object (Object.keys(data[0])), only id,name,role are written. The department and tags columns from subsequent records are silently discarded.

The Fix: Pass through all records to construct a complete union of unique keys before generating the CSV header:

const allKeys = Array.from(new Set(records.flatMap(obj => Object.keys(obj))));
Enter fullscreen mode Exit fullscreen mode

When generating rows, map through allKeys and output an empty string for missing keys to preserve column alignment.

3. Deep Nesting vs. 1-to-Many Array Flattening

Hierarchical JSON does not naturally map to a 2D grid. For 1-to-1 nested objects, recursive dot-notation flattening works cleanly (e.g., user.profile.address becomes a column).

However, arrays inside objects represent a structural impedance mismatch:

{
  "orderId": 8941,
  "items": [{ "sku": "A12", "qty": 2 }, { "sku": "B99", "qty": 1 }]
}
Enter fullscreen mode Exit fullscreen mode

Calling String(row.items) results in [object Object],[object Object].

When inspecting or converting payloads quickly in your browser, a client-side utility like Nutilz free JSON to CSV converter lets you pick your flattening delimiter and customize how nested arrays are structured.

In automated pipelines, decide explicitly:

  1. JSON-stringifying: Store nested arrays as raw JSON strings inside quoted CSV cells.
  2. Denormalization: Emit multiple CSV rows per parent record, replicating root attributes for each child item.

4. Spreadsheet Type Coercion: Leading Zeros and Precision Loss

Even when your CSV is RFC-compliant, opening it in Microsoft Excel or Google Sheets can corrupt values:

  • Leading zeros: "01234" is parsed as an integer and rendered as 1234.
  • 64-bit IDs: Numbers exceeding 15 digits (e.g., Snowflake IDs like 849182740192837461) surpass IEEE 754 float limits, converting tail digits to zeros (849182740192837000) or scientific notation (8.4918E+17).

The Fix: For human viewing in Excel, format string identifiers as formula expressions:

id,zip_code
="849182740192837461",="01234"
Enter fullscreen mode Exit fullscreen mode

For automated downstream consumers, keep standard string quotes and ensure your ingestion parser reads identifiers as strings.

5. Missing UTF-8 Byte Order Mark (BOM)

JSON is UTF-8 encoded without a BOM. But when Excel on Windows opens a CSV without a UTF-8 BOM, it falls back to ANSI/Windows-1252 encoding. Any international characters, accents, or currency symbols (£, €, München, 日本語) turn into garbled mojibake (München).

The Fix: Prepend the 3-byte UTF-8 BOM (\uFEFF in JavaScript, b"\xef\xbb\xbf" in Python) to the CSV output:

const csvWithBOM = "\uFEFF" + csvContent;
Enter fullscreen mode Exit fullscreen mode

This signals Excel to parse the file as UTF-8 immediately without prompting.

Conclusion

Transforming hierarchical JSON into tabular CSV requires handling RFC 4180 escaping, sparse key unions, nested collections, spreadsheet type coercion, and UTF-8 encoding markers.

Whenever you need to quickly inspect, flatten, and export JSON payloads without writing one-off scripts or uploading sensitive data to third-party servers, Nutilz free JSON to CSV converter handles nested structures and custom delimiters entirely in your browser.

Top comments (0)