How to Convert JSON to Excel (Online, Power Query & Python Guide)

To convert a JSON file into a Microsoft Excel (.xlsx) spreadsheet without uploading sensitive data to third-party cloud servers, use a client-side in-browser converter like EasyExtract to recursively flatten nested keys into columns and download an authentic OpenXML (.xlsx) workbook directly from browser RAM. Alternatively, within Microsoft Excel, navigate to Data > Get Data > From File > From JSON to parse the document using the built-in Power Query engine, or execute an automated Python command using pandas.read_json() and df.to_excel().

JavaScript Object Notation (JSON, standardized under RFC 8259 and ECMA-404) is the universal lingua franca of modern software development, web APIs, microservices, and NoSQL databases like MongoDB and Elasticsearch. However, while JSON excels at representing deeply nested, hierarchical, and schema-less data structures for computer applications, business analysts, accountants, data scientists, and operations managers require two-dimensional tabular grids—rows and columns—to perform pivot table analysis, financial forecasting, charting, and audit reconciliation inside Microsoft Excel, Google Sheets, or Apple Numbers.

Bridging this structural divide is not as trivial as renaming a file extension from .json to .xlsx or .csv. If a JSON document contains nested child objects, arrays of values, or variable schemas across records, standard spreadsheet applications will fail to render the data correctly, displaying either blank cells, truncated strings, or cryptic errors like [object Object]. Furthermore, converting sensitive enterprise data—such as customer payment records, patient health logs, employee PII, or internal metrics—by pasting it into untrusted, ad-supported third-party conversion websites poses severe data leakage, compliance, and regulatory liabilities under GDPR, HIPAA, and SOC 2 frameworks.

This comprehensive technical guide explains the architectural mechanics of converting hierarchical JSON into relational spreadsheets, details four proven methods across client-side web tools, native Microsoft Excel Power Query, Python automation, and command-line utilities, and provides actionable solutions for flattening complex nested keys without data loss.

—

Core Concepts: Hierarchical Trees vs Two-Dimensional Grids

To understand why JSON-to-Excel conversion requires deliberate data transformation, it is necessary to examine the fundamental architectural discrepancy between the two document models:

The Flattening Challenge: Resolving Nested Hierarchies

When transforming an array of JSON objects into spreadsheet rows, the primary challenge is hierarchical flattening. Consider a customer record extracted from an e-commerce order API:

{
  "order_id": 98124,
  "customer": {
    "name": "Sarah Connor",
    "contact": {
      "email": "sarah@cyberdyne.org",
      "phone": "+1-555-0199"
    }
  },
  "items": [
    {"sku": "CPU-T800", "qty": 1, "price": 4500.00},
    {"sku": "ARM-M9", "qty": 2, "price": 1200.00}
  ],
  "is_paid": true
}

To represent this structure in Excel without data loss, a converter must make architectural decisions:

• Object Flattening (Dot Notation): The nested object customer.contact.email is flattened by concatenating key names with a separator period, producing the explicit spreadsheet column header customer.contact.email.
• Primitive Array Normalization: Arrays of scalar values (such as tags or user roles) are serialized into a single string with a recognizable delimiter (e.g., Security; Audits; Compliance).
• Complex Array Normalization (Relational Denormalization vs In-Cell Serialization): For lists of nested objects (such as line items), the converter must either serialize the array into structured JSON strings within the cell or split the parent order across multiple rows (relational unnesting).

—

Method 1: Instant Client-Side Conversion via EasyExtract (Zero Cloud Upload)

For ad-hoc conversions of JSON files, API snippets, or database exports where data privacy, speed, and zero software configuration are paramount, in-browser client-side conversion provides the optimal balance of security and convenience.

Step-by-Step Conversion Procedure:

1. Open the EasyExtract JSON to Excel Tool in any modern desktop or mobile browser (Chrome, Edge, Firefox, Safari).
2. Input your data: Drag and drop your .json or .txt file directly onto the upload zone, or paste your raw JSON string or API response into the text area.
3. Configure flattening: Ensure “Flatten nested keys” is checked. This guarantees that deep paths like user.address.street are transformed into standalone columns rather than rendering as unreadable [object Object] strings.
4. Click Convert to Excel. The engine evaluates the JSON tree, calculates the union of all unique keys across all records, flattens the hierarchy, and generates a real-time spreadsheet preview.
5. Download your workbook:
   • Click Download Excel (.xlsx) to save an authentic Microsoft OpenXML workbook formatted with native number and boolean types.
   • Click Download CSV to export a lightweight UTF-8 encoded text table for database imports.
   • Click Copy TSV to paste the table directly into Google Sheets or an existing open spreadsheet with a single keystroke (Ctrl+V / Cmd+V).

—

Method 2: Native Microsoft Excel Power Query Method (`Data > From JSON`)

If you have Microsoft 365, Excel 2019, or Excel 2016 installed, you can leverage Microsoft’s enterprise ETL engine, Power Query (Get & Transform Data), to import and shape JSON files natively without installing external plugins.

Step 1: Ingest the JSON Document

1. Launch Microsoft Excel and create a blank workbook.
2. Click on the Data tab on the main ribbon.
3. Click Get Data > From File > From JSON.
4. Browse to your local file system, select your .json file, and click Import.

Step 2: Transform and Expand the Records

1. In the Power Query Editor ribbon, look at the Transform tab and click To Table.
2. In the To Table dialog, accept the default delimiter (None) and extra column handling (Show as errors), then click OK.
3. You will now see a single column named Column1 containing multiple rows labeled [Record].
4. Click the Expand icon (two diverging arrows) located on the right side of the Column1 header.
5. Uncheck “Use original column name as prefix” (unless you prefer prefixed names), verify that all target attributes are checked, and click OK.

Step 3: Flatten Nested Objects

If any attributes in your records contained nested objects, those columns will now display nested [Record] or [List] entries:

• For nested Records: Click the expand icon on that specific column header and choose which child fields to project into new adjacent columns.
• For nested Lists (Arrays): Click the expand icon and select either “Expand to New Rows” (which duplicates the parent row for each list item) or “Extract Values…” (which joins scalar items with a delimiter like comma or semicolon).

Step 4: Load Data into the Worksheet

Once all columns are expanded and formatted, click Close & Load on the Home tab. Power Query will inject the shaped data into a polished Excel Table (ListObject) complete with column filters, zebra striping, and typed data formatting.

—

Method 3: Automated Python Script via Pandas & OpenPyXL

For data engineers, DevOps specialists, and automated reporting pipelines, converting thousands of incoming JSON payloads to Excel is most reliably achieved using Python and the high-performance pandas library.

Installation Prerequisites

Ensure you have Python 3.8+ installed along with pandas and the openpyxl Excel engine:

pip install pandas openpyxl

Python Script: Recursive Normalization and Export

The standard pandas.read_json() function works well for flat arrays, but will fail to unpack nested sub-dictionaries. To achieve full flattening parity with professional converters, use pandas.json_normalize():

import json
import pandas as pd

def convert_json_to_excel(json_filepath: str, excel_filepath: str):
    """
    Reads a JSON file, flattens arbitrary nested dictionaries using dot notation,
    serializes nested arrays, and exports a clean multi-sheet .xlsx workbook.
    """
    with open(json_filepath, 'r', encoding='utf-8') as f:
        raw_data = json.load(f)
    
    # Ensure data is a list of records
    records = raw_data if isinstance(raw_data, list) else [raw_data]
    
    # Flatten nested dictionaries (e.g. user.address.geo.lat)
    df = pd.json_normalize(records, sep='.')
    
    # Clean up complex array objects that remain as lists inside cells
    for col in df.columns:
        if df[col].apply(lambda x: isinstance(x, list)).any():
            df[col] = df[col].apply(
                lambda val: '; '.join(map(str, val)) if isinstance(val, list) else val
            )
            
    # Write to Excel workbook with OpenPyXL engine
    with pd.ExcelWriter(excel_filepath, engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name='Data_Export', index=False)
        
    print(f"Successfully converted {len(df)} rows and {len(df.columns)} columns to {excel_filepath}")

if __name__ == '__main__':
    convert_json_to_excel('sample_api_response.json', 'output_report.xlsx')

Key Advantages of Programmatic Pandas Normalization:

• Batch Conversion: Process entire directories containing gigabytes of daily API logs into formatted Excel workbooks via automated cron jobs.
• Custom Type Casting: Enforce ISO datetime parsing, currency rounding, and categorical indexing before writing to disk.
• Multi-Worksheet Architectures: Write relational parent and child tables into separate tabs within the identical .xlsx container linked by primary foreign keys.

—

Method 4: Command-Line Conversion via JQ, Miller & Csvkit

On Linux and macOS servers where GUI applications and Python environments may not be available, Unix command-line pipes allow lightning-fast streaming conversion of massive JSON and JSON Lines (NDJSON) streams.

Option A: Using Miller (`mlr`) — The Swiss Army Knife of Data

Miller natively understands JSON, CSV, and tabular data, automatically flattening nested records on the fly:

# Install via Homebrew (Mac) or apt (Ubuntu)
brew install miller

# Convert JSON array to CSV (which Excel opens natively)
mlr --ijson --ocsv --flatten-full-path cat input.json > output.csv

# Convert JSON Lines (NDJSON) directly to TSV
mlr --ijson --onjson --otsv cat server_logs.ndjson > output.tsv

Option B: Using JQ & Csvkit

If you have jq and csvkit installed, you can project specific keys directly into an Excel workbook:

# Extract keys with jq, convert to CSV, then compile to XLSX
jq -r '.[] | [.order_id, .customer.name, .customer.contact.email, .is_paid] | @csv' orders.json \
  | csvformat -D "," \
  | in2csv -f csv \
  | csvsql --query "SELECT * FROM stdin" > orders.csv

—

Comparison Matrix: Choosing the Right Conversion Tool

—

Solving Common JSON-to-Excel Conversion Pitfalls

1. How to Fix `[object Object]` Cells in Excel

When an Excel cell displays [object Object], it means a naive conversion utility attempted to coerce a complex JavaScript dictionary into a string without inspecting its internal keys. To resolve this:

• Use a converter with recursive flattening enabled (like EasyExtract).
• In Excel Power Query, click on the diverging double arrows at the top of the problematic column and expand the child attributes.
• In Python, use pd.json_normalize() instead of raw pd.DataFrame().

2. Preserving Leading Zeros (Zip Codes, Phone Numbers & Account IDs)

Excel notoriously attempts to guess column data types. When an account number or postal code like "00412" is imported, Excel frequently strips the leading zeros and casts the value as the integer 412. EasyExtract safeguards these values during .xlsx compilation by storing identifier strings as explicit OpenXML inline text nodes (t="inlineStr"), preventing Excel’s auto-formatting engine from destroying critical leading zeroes.

3. Handling Line-Delimited JSON (NDJSON)

Log aggregators (Datadog, AWS CloudWatch, Logstash) export data as NDJSON (Newline Delimited JSON), where each line is an independent JSON object without surrounding array brackets [...] or comma separators. Standard JSON parsers fail with SyntaxError: Unexpected token { after JSON. EasyExtract automatically detects newline-delimited syntax and buffers individual lines into a consolidated schema matrix seamlessly.

—

Frequently Asked Questions (PAA)