Converting JSON to CSV: Data Export Patterns for Developers

Why Convert JSON to CSV?

JSON is ideal for hierarchical data exchanged between services. CSV is the universal format for tabular data in spreadsheets, data analysis tools, and import pipelines. Converting between them is a core data engineering task — exporting a database query result to Excel, preparing a report for a non-technical stakeholder, or feeding data into a pandas DataFrame.

The Basic Structure

JSON-to-CSV conversion assumes the JSON is an array of objects where each object represents a row. The object keys become column headers.

[
  { "name": "Alice", "age": 30, "city": "London" },
  { "name": "Bob",   "age": 25, "city": "Paris"  }
]

name,age,city Alice,30,London Bob,25,Paris

Handling Special Characters

CSV values must be quoted when they contain commas, newlines, or double quotes. The RFC 4180 standard defines the rules:

• Fields containing commas must be wrapped in double quotes: "London, UK"
• Fields containing double quotes must double the quote character: "He said ""hello"""
• Fields containing newlines must be wrapped in double quotes

function escapeCSV(value) {
  const str = String(value ?? '');
  if (str.includes(',') || str.includes('"') || str.includes('\n')) {
    return '"' + str.replace(/"/g, '""') + '"';
  }
  return str;
}

Collecting All Keys (Column Headers)

When records have inconsistent keys (sparse data), you need to collect all unique keys across all records before building the header row:

function jsonToCsv(records) {
  const keys = [...new Set(records.flatMap(r => Object.keys(r)))];
  const header = keys.map(escapeCSV).join(',');
  const rows = records.map(r =>
    keys.map(k => escapeCSV(r[k] ?? '')).join(',')
  );
  return [header, ...rows].join('\n');
}

Flattening Nested Objects

CSV is inherently flat — it cannot represent nested objects or arrays. You have two options:

Option 1: Dot-notation flattening

// Input
{ "user": { "name": "Alice", "age": 30 }, "score": 99 }

// Flattened keys
{ "user.name": "Alice", "user.age": 30, "score": 99 }

Option 2: Stringify nested values

For arrays or objects that don't flatten cleanly, serialize them as a JSON string within the CSV cell. Excel will display it as a text string.

Excel Compatibility

When opening CSV files, Excel uses the system locale's list separator (often semicolon in Europe, not comma). To ensure compatibility:

• Add a BOM () at the start for Excel to detect UTF-8 encoding
• Consider offering both comma and semicolon delimiters
• CRLF (\r\n) line endings are more compatible with Windows tools than LF alone

// Excel-safe UTF-8 CSV download
const bom = '\uFEFF';
const blob = new Blob([bom + csvContent], {
  type: 'text/csv;charset=utf-8;'
});

Choosing the Right Tool

• One-off export: Use an online converter like this tool — paste JSON, download CSV
• Server-side pipeline: Libraries like json2csv (Node.js) or Python's csv module with pandas
• Database export: Most databases support COPY TO CSV or equivalent, skipping JSON entirely