How to Convert Nested JSON to CSV for Excel: Objects, Arrays, and Missing Fields

An API response can look like a table until you try to open it in Excel. The records sit inside data.items, addresses are nested objects, tags are arrays, and one record contains a field the others do not have.

To convert that response into usable rows, first choose the records array, then decide how the nested values should appear in cells. The JSON to CSV Converter supports an array path, nested-object flattening, and a table preview so you can check those choices before downloading.

This walkthrough uses synthetic data you can download and compare with the expected output.

Start with the records you want to turn into rows

Download the example API response. Its two records are inside data.items:

{
  "data": {
    "items": [
      {
        "id": "00127",
        "name": "Ana López",
        "address": { "city": "Boston", "postalCode": "02108" },
        "tags": ["customer", "newsletter"],
        "note": "Call Tuesday, after lunch"
      },
      {
        "id": "00128",
        "name": "Noah Khan",
        "address": { "city": "Lahore" },
        "tags": [],
        "note": null,
        "active": true
      }
    ]
  },
  "total": 2
}

Each object in items should become one row. The outer data object and total value describe the response; they are not additional customer rows.

Open JSON to CSV, paste the example or select its file, and open Conversion options. Enter data.items in Array path. Keep the header row and nested-object flattening enabled, and select a comma separator with automatic quoting.

Convert the input and review the table before downloading.

What flattening does to objects

The address value contains another object. With flattening enabled, its fields become separate columns:

JSON field	CSV column	First record's value
id	id	00127
address.city	address.city	Boston
address.postalCode	address.postalCode	02108

The dots are part of the CSV column names. They preserve the path to a value without putting an entire object into one cell.

If you disable flattening, nested objects remain serialized JSON text inside cells. That can be useful when another system expects a JSON string, but separate address columns are easier to filter and sort in a spreadsheet.

Check for ambiguous source keys before relying on dotted names. An object that already has a literal key named address.city can conflict with the same path created from a nested address object. Rename ambiguous keys or transform that data explicitly before exporting.

Arrays stay in one cell

The converter handles a nested array differently from a nested object. In this example, tags becomes JSON text in one cell:

["customer","newsletter"]

It does not create one row per tag. The empty array in the second record stays [].

That distinction matters for exports such as orders and line items. If one order contains five products, this converter does not automatically turn it into five order-product rows. Use a transformation that explicitly expands the array if that is the table you need.

Keeping arrays as text is still useful when you want one row per original record and do not need to filter individual array members in Excel.

Missing fields and nulls become blank cells

The second record has no postal code. The first has no active field. The converter collects columns across the records, so both columns remain present, with blanks where a value is absent.

In the default output, null also becomes an empty cell. The CSV therefore does not preserve the distinction between a missing field, a null value, and an empty string in a way you can reliably recover later. Keep the original JSON when that distinction matters.

The expected output is:

id,name,address.city,address.postalCode,tags,note,active
00127,Ana López,Boston,02108,"[""customer"",""newsletter""]","Call Tuesday, after lunch",
00128,Noah Khan,Lahore,,[],,true

Download the expected CSV to compare with your own conversion. The doubled quotes inside the tags field escape literal quote characters; they are not extra tags. The comma inside the note stays within one quoted cell.

Import the CSV into Excel deliberately

CSV contains text and separators, without Excel workbook formatting or reliable column-type instructions. Double-clicking the file can let Excel guess types you did not intend.

Use Data → From Text/CSV in an Excel version that provides that import workflow. Choose the file, inspect the delimiter and text encoding, and use Transform Data when you need to control column types. Set identifiers such as id and address.postalCode to Text before an automatic number conversion strips their leading zeros. If a Changed Type step has already converted them, remove or adjust that step before loading.

Microsoft's text and CSV import guidance explains the available import paths. Menu labels and options vary by Excel version.

For this example, check that 00127 and 02108 survive exactly, and that López displays correctly. If everything appears in one column, choose the separator used by the export. If accented characters appear incorrectly, select UTF-8 in the import controls.

Do not assume quotation marks force Excel to treat a value as text. CSV quoting protects the field structure; spreadsheet type inference is a separate step.

What if the file is JSON Lines?

JSON Lines puts one JSON value on each line instead of wrapping all records in one array. The converter accepts this input too.

Download the same records as JSON Lines, clear Array path, and convert it. It contains the two record objects directly, so data.items is no longer the right path.

If an error identifies a line, check that line as a complete JSON object. Use the JSON Formatter & Validator for syntax checking or the JSON Tree Viewer to inspect the structure of an ordinary JSON document.

When Power Query is a better fit

For a one-off conversion, a small example and a preview can be enough. For a report you refresh repeatedly, a saved import and transformation pipeline is usually easier to maintain than manually converting each response.

Microsoft's Power Query JSON connector can import JSON and apply transformations to produce a table. It is worth considering when you need repeatable expansion, several input files, or more control over types and nested records.

Keep the output's purpose clear: a CSV export is a flattened view of the JSON, not a complete backup of its structure. Preserve the source, check a few difficult rows, and then convert your own JSON to CSV. If you need test records first, the mock-data guide shows how to create synthetic inputs.