How to Convert a JSON Array to an Excel Table

The vast majority of real-world JSON data is structured as an array of objects — a list of records where each record has the same set of fields. This structure maps perfectly to a spreadsheet table, making the conversion straightforward in most cases.

But there are several variations of JSON arrays that each require a slightly different approach. This guide covers all of them.

The Standard Case: Array of Uniform Objects

This is the ideal structure for Excel conversion:

[
  { "id": 1, "product": "Widget A", "price": 9.99,  "qty": 120 },
  { "id": 2, "product": "Widget B", "price": 14.50, "qty": 85  },
  { "id": 3, "product": "Widget C", "price": 7.25,  "qty": 200 }
]

Every object has the same four keys. The conversion is direct:

Handling Inconsistent Keys

Sometimes not every object has the same fields. This happens when optional fields are omitted rather than set to null:

[
  { "id": 1, "name": "Alice", "phone": "555-0100" },
  { "id": 2, "name": "Bob"  },                    ← no phone
  { "id": 3, "name": "Carol", "phone": "555-0300", "fax": "555-0301" } ← extra field
]

The correct behavior is to create a column for every unique key across all objects, and leave cells empty where a record does not have that key. This produces a sparse table:

Handling Arrays with Nested Objects

When each object contains nested sub-objects, the nested fields need to be flattened using dot notation:

[
  {
    "id": 1,
    "name": "Alice",
    "address": {
      "city": "London",
      "country": "UK"
    }
  }
]

Flattened to:

In Python, pd.json_normalize() handles this automatically:

import pandas as pd
import json

with open('data.json') as f:
    data = json.load(f)

df = pd.json_normalize(data)   # flattens nested objects
df.to_excel('output.xlsx', index=False)

Handling Arrays Within Objects

When each record contains an array field, you have two options: join the array values into a single cell, or expand the array into multiple rows.

[
  { "id": 1, "name": "Alice", "tags": ["analyst", "manager"] }
]

Option A: Join into a single cell

Result: analyst, manager in one cell. Simple and compact.

# Python
df = pd.json_normalize(data)
# The 'tags' column will contain Python lists — convert to comma-separated strings
df['tags'] = df['tags'].apply(lambda x: ', '.join(x) if isinstance(x, list) else x)
df.to_excel('output.xlsx', index=False)

Option B: Expand to multiple rows (explode)

Result: one row per tag, with id and name repeated for each tag.

df = pd.json_normalize(data)
df = df.explode('tags')  # one row per tag
df.to_excel('output.xlsx', index=False)

Large JSON Arrays — Performance Tips

For JSON arrays with thousands or millions of records, keep these tips in mind:

• Excel row limit: Excel supports a maximum of 1,048,576 rows. If your JSON array has more records, you will need to split the output across multiple sheets or use a database instead.
• pandas chunking: If your JSON file is too large to load into memory at once, use chunked reading with a streaming JSON parser like ijson.
• Column limit: Excel supports up to 16,384 columns. Deeply nested JSON with many unique keys could theoretically exceed this, though it is rare in practice.

# Split output across multiple sheets if needed
import pandas as pd
import math

df = pd.read_json('large_data.json')
max_rows = 1048575  # Excel limit minus header row

num_sheets = math.ceil(len(df) / max_rows)

with pd.ExcelWriter('output.xlsx') as writer:
    for i in range(num_sheets):
        chunk = df.iloc[i * max_rows : (i + 1) * max_rows]
        chunk.to_excel(writer, sheet_name=f'Sheet{i+1}', index=False)

Flat Arrays vs Arrays of Objects

Not all JSON arrays are arrays of objects. A flat array of primitives is a different case:

["London", "Paris", "Tokyo", "New York"]

This becomes a single column in Excel with each value in its own row. Most converters handle this, but make sure the tool you use does not misinterpret it as a single-row array.