JSON to CSV Converter Guide: Export API Data to Spreadsheets

Modern APIs return data as JSON. But the people who need to analyze that data — business analysts, product managers, finance teams — live in spreadsheets. Converting JSON to CSV bridges that gap, letting you take API responses or database exports and open them directly in Excel, Google Sheets, or a Python pandas workflow.

This guide explains how the conversion works, shows a complete worked example, covers the tricky parts (nested objects, arrays within arrays), and explains when JSON-to-CSV conversion is inherently lossy.

Common Use Cases

• Exporting API data to a spreadsheet — fetching order data, user records, or analytics from an API and sharing it with stakeholders in CSV format
• Database exports for reporting — exporting a MongoDB collection or a REST API response for use in Excel pivot tables or Google Sheets dashboards
• Data preparation for machine learning — many ML libraries (scikit-learn, pandas) prefer flat tabular data; JSON records often need to be flattened to CSV first
• Debugging API responses — converting a JSON array to CSV makes it easy to visually scan for missing or malformed records in a spreadsheet
• Archiving or auditing — flat CSV files are simple to store, search, and share across systems that may not support JSON

How the Conversion Works: Flattening JSON

For a simple JSON array of flat objects, the conversion is direct: the keys of the first object become the CSV header row, and each subsequent object becomes a row. Each key's value fills the corresponding column.

The complexity arises with nested JSON. JSON supports objects within objects and arrays of objects — structures that have no direct equivalent in the flat rows-and-columns model of CSV. Converting nested JSON to CSV requires "flattening" it.

Worked Example: Flat JSON to CSV

Input JSON:

[
  {"id": 1, "name": "Alice", "email": "alice@example.com", "plan": "pro"},
  {"id": 2, "name": "Bob", "email": "bob@example.com", "plan": "free"},
  {"id": 3, "name": "Carol", "email": "carol@example.com", "plan": "pro"}
]

Output CSV:

id,name,email,plan
1,Alice,alice@example.com,pro
2,Bob,bob@example.com,free
3,Carol,carol@example.com,pro

Clean and straightforward. This opens directly in Excel or Google Sheets.

Handling Nested Objects

When an object contains nested objects, the converter flattens them using dot notation for column names. For example:

[
  {
    "id": 1,
    "name": "Alice",
    "address": {
      "city": "Portland",
      "country": "US"
    }
  }
]

Flattened to CSV:

id,name,address.city,address.country
1,Alice,Portland,US

The nested address object becomes two columns: address.city and address.country. This is lossless — all the data is preserved, just restructured.

Handling Arrays Within Objects

Arrays within JSON objects are the hardest case. Consider a user record with multiple tags:

[
  {"id": 1, "name": "Alice", "tags": ["admin", "beta-tester"]}
]

There is no clean single-row representation for this in CSV. Converters handle it one of a few ways:

• Serialize to string: The array becomes a quoted string in the cell: "admin,beta-tester". Simple but not easily parseable in Excel.
• Expand to multiple columns: Creates tags.0, tags.1, etc. Works if all rows have the same number of tags; breaks badly if they differ.
• Expand to multiple rows: Creates one row per tag value. This is the relational approach and is most correct, but it changes the shape of the data significantly.

For most use cases, serializing arrays to a delimited string per cell is the most practical approach when you need a quick export.

Handling Inconsistent Keys

Real-world JSON APIs sometimes return records with different keys. One object might have a phone field while another does not. A good converter uses the union of all keys it finds as the header row, and fills empty cells with blank or null values. Always check your CSV output for unexpected empty columns.

Using the Output in Spreadsheet Tools

Excel

Open Excel and use File > Open or import via the Data ribbon. When importing a .csv file, Excel shows a Text Import Wizard — confirm that the delimiter is a comma and that the encoding is UTF-8 to avoid garbled special characters.

Google Sheets

In Google Sheets, use File > Import and upload the CSV. Choose "Comma" as the separator type and "No" for "Convert text to numbers and dates" if you want to preserve values exactly as strings.

Python pandas

import pandas as pd

df = pd.read_csv('output.csv')
print(df.head())

Pandas reads CSV files natively and attempts type inference — numbers will become integers or floats, and date strings may be parsed automatically. If this causes issues, use dtype=str to load everything as strings first.

When JSON to CSV Is Lossy

CSV is a flat, two-dimensional format. JSON can represent arbitrarily complex hierarchical data. Any time you convert from JSON to CSV, you risk losing information:

• Deeply nested objects flatten to long dot-notation column names that may be difficult to work with in a spreadsheet
• Arrays of objects within arrays — an order with multiple line items, each with multiple attributes — cannot be losslessly represented in a single CSV without choosing a specific flattening strategy
• Data types — JSON distinguishes between null, false, 0, and "". CSV represents all of these as empty or the string "false"/"0" — the distinction can be lost
• Unicode and special characters — if the CSV is not properly encoded as UTF-8, non-ASCII characters may be corrupted

Tips for Clean Output

• Consistent keys: Ensure all objects in your JSON array have the same set of keys. Missing keys become empty cells.
• Avoid null in key fields: Null values in identifier columns (like id) create confusing rows in spreadsheets.
• Pre-flatten complex fields: If you control the API or the JSON generation, flatten nested structures before exporting rather than relying on the converter to do it.
• Wrap strings with commas in quotes: If any string value contains a comma, the CSV converter should automatically quote the field. Verify this in your output before opening in Excel.

Summary

Converting JSON to CSV maps the keys of each object to column headers and each object to a row. Flat JSON converts cleanly. Nested objects are flattened with dot-notation column names. Arrays within objects require a strategy choice: serialize to string, expand to columns, or expand to rows. The conversion is lossy for complex structures — CSV cannot represent all of JSON's expressiveness. For a clean result, start with flat or lightly nested JSON, use consistent keys, and verify your output in a spreadsheet before distributing.