Convert Excel to Nested JSON — When Flat Arrays Are Not Enough

Most Excel-to-JSON converters — including ours — produce flat JSON arrays: a list of objects where each row becomes one object with no nesting. For many use cases, that's perfect. But some APIs, systems, and formats expect nested JSON: arrays within arrays, parent-child relationships, objects with embedded sub-objects. This guide explains the difference, when flat JSON is actually fine, and how to produce nested output when you genuinely need it.

Flat JSON vs Nested JSON — What's the Difference?

Flat JSON (what our converter produces):

[
  { "order_id": "001", "customer": "Alice", "item": "Widget", "qty": 3 },
  { "order_id": "001", "customer": "Alice", "item": "Gadget", "qty": 1 },
  { "order_id": "002", "customer": "Bob", "item": "Widget", "qty": 5 }
]

Nested JSON (grouped by order, with line items as an array):

[
  {
    "order_id": "001",
    "customer": "Alice",
    "items": [
      { "item": "Widget", "qty": 3 },
      { "item": "Gadget", "qty": 1 }
    ]
  },
  {
    "order_id": "002",
    "customer": "Bob",
    "items": [ { "item": "Widget", "qty": 5 } ]
  }
]

The nested format groups data hierarchically. The flat format repeats parent data for each child row. Excel is inherently flat (rows and columns) — getting nested JSON requires reshaping the data after export.

When Flat JSON Is Actually Fine (Most Cases)

Before building a complex nesting pipeline, check whether flat JSON actually works for your use case:

• Database imports: Most databases (PostgreSQL, MySQL, MongoDB) accept flat arrays and import them as individual rows. Nesting is unnecessary.
• Spreadsheet/reporting tools: Tools that consume JSON for charts or reports usually expect flat arrays — one object per data point.
• REST APIs: Many APIs accept flat arrays for batch operations. Check the API documentation — if it accepts flat rows, save yourself the extra work.
• CSV-like use cases: If you're passing data between systems that would normally use CSV, flat JSON is the exact equivalent.

The flat array from our Excel to JSON converter is the right format for the majority of real-world use cases. Nesting is required by a minority of APIs and formats.

Sell Custom Apparel — We Handle Printing & Free Shipping

How to Produce Nested JSON from an Excel File

There is no one-click solution for all nested structures — the nesting logic depends on your specific data relationships. Here are the main approaches:

Option 1 — Python (most flexible):

import pandas as pd, json
df = pd.read_excel('orders.xlsx')
result = []
for order_id, group in df.groupby('order_id'):
    result.append({
        'order_id': order_id,
        'customer': group['customer'].iloc[0],
        'items': group[['item', 'qty']].to_dict('records')
    })
print(json.dumps(result, indent=2))

This groups by order_id and embeds line items as a nested array. The logic changes based on your data structure — which column is the parent, which are children, what fields belong at which level.

Option 2 — Excel structure + manual reshaping:

Some users separate their spreadsheet into a "parent" sheet (one row per order) and a "child" sheet (one row per line item with an order_id foreign key), convert both to JSON separately using our tool, then merge them programmatically. This keeps the conversion step simple and moves complexity to the merge step.

Nested JSON from a Single Flat Sheet — JSON Cell Trick

One clever workaround: put pre-formatted JSON in an Excel cell. For example, in your "items" column, type the JSON array directly as a string:

order_id | customer | items
001      | Alice    | [{"item":"Widget","qty":3},{"item":"Gadget","qty":1}]
002      | Bob      | [{"item":"Widget","qty":5}]

When converted with our tool, the "items" column contains string values (the JSON array as a string). These strings can then be parsed by your application using JSON.parse() to produce properly nested objects.

This approach is useful when you're preparing data for import into a system that expects nested JSON but you want to maintain the spreadsheet in a human-readable format.

Practical Limitations to Know

A few things to keep in mind when working with nested JSON and Excel:

Deep nesting is hard to maintain in spreadsheets. Spreadsheets are naturally flat — trying to represent 3+ levels of nesting in a flat table gets complex fast. If your data is truly deeply hierarchical, a spreadsheet may not be the right source format.

No browser tool currently produces nested JSON directly. This includes our tool. The nesting logic is too application-specific to automate in a general-purpose converter. You'll need Python, JavaScript, or another processing step.

Flat JSON with joins is often equivalent. If you're loading data into a database or an application that can do its own nesting, flat JSON is fine — let the application do the grouping. This is often cleaner than pre-nesting.

Frequently Asked Questions

Does the Excel to JSON converter support nested arrays?

No — it produces flat JSON arrays where each row becomes one object. Nested JSON requires reshaping the data after export, typically using Python or JavaScript.

Can I use Power Query in Excel to create nested output?

Power Query can produce nested JSON via the JSON.FromValue() M function, but this requires writing M code and setting up a custom query. For straightforward cases, the Python approach is usually easier.

What is the best Python library for Excel to nested JSON?

pandas is the standard — use groupby() to group rows into parent-child relationships and to_dict("records") to serialize child rows. For very large files or streaming, consider using openpyxl directly for row-by-row processing.

Can I convert an Excel file with multiple sheets into a single nested JSON?

Yes with Python — read each sheet into a DataFrame, then build a dictionary keyed by sheet name. Each sheet becomes a nested array. Our browser tool currently converts one sheet at a time to separate flat arrays.