Excel JSON CSV Tutorial

How to Open JSON in Excel: The Complete Guide from CSV to JSON

Published: July 2, 2026 · 8 min read · Keywords: how to open json in excel, excel json import

Table of Contents

  1. Why Convert JSON to CSV for Excel?
  2. Method 1: Using Our Online Converter (Easiest)
  3. Method 2: Excel Power Query
  4. Method 3: VBA Macro Import
  5. Method 4: Python Script
  6. Tips & Best Practices
  7. FAQ

Why Convert JSON to CSV for Excel?

JSON is the universal language of web APIs, but Excel speaks CSV. When you need to analyze API data, share reports with non-technical colleagues, or clean up datasets, converting JSON to CSV is the essential first step.

📊 JSON (structured tree) → CSV (flat table) → Excel spreadsheet

Here's a quick comparison:

Feature JSON CSV
Excel Compatibility Requires conversion Native support
Nested Data Supported Flat structure only
Human Readability Moderate High (in spreadsheets)
File Size Larger (verbose syntax) Smaller

1 Method 1: Using Our Online Converter (Easiest)

The fastest way to convert JSON to CSV — no installation, no coding required.

Step-by-Step

  1. Go to our JSON to CSV Converter
  2. Upload your .json file or paste JSON content directly
  3. Click "Convert to CSV"
  4. Preview the result in the table view
  5. Click "Download CSV" to save the file
  6. Open the CSV file in Excel — done!

💡 Tip: Our converter adds a UTF-8 BOM header automatically, so Excel will correctly display Chinese, Japanese, Arabic, and other non-ASCII characters.

Example Input

[
  {
    "name": "Alice",
    "department": "Engineering",
    "salary": 85000,
    "hire_date": "2024-03-15",
    "is_remote": true
  },
  {
    "name": "Bob",
    "department": "Marketing",
    "salary": 72000,
    "hire_date": "2023-08-01",
    "is_remote": false
  }
]

Result in Excel

name department salary hire_date is_remote
Alice Engineering 85000 2024-03-15 true
Bob Marketing 72000 2023-08-01 false

2 Method 2: Excel Power Query

Excel's built-in Power Query (Get & Transform) can import JSON directly — but it requires some manual work.

Steps

  1. Open Excel → Go to Data tab → Get Data → From File → From JSON
  2. Select your .json file
  3. In the Power Query Editor, you'll see the raw JSON structure
  4. Click "Into Table" to convert the list to a table
  5. Expand any nested columns by clicking the expand icon
  6. Click "Close & Load" to import into your worksheet
💻 Excel → Data → Get Data → From File → From JSON

⚠ Note: Power Query may struggle with large JSON files (>10 MB) or deeply nested structures. For complex data, converting to CSV first is more reliable.

Power Query M Code Example

// Advanced Editor — M code for JSON import
let
    Source = Json.Document(File.Contents("C:\data\users.json")),
    toTable = Table.FromList(Source, Splitter.SplitByNothing(),
            null, null, ExtraValues.Error),
    expanded = Table.ExpandRecordColumn(toTable, "Column1",
            {"name", "email", "age"},
            {"name", "email", "age"})
in
    expanded

3 Method 3: VBA Macro Import

For advanced users who want to automate JSON import in Excel, here's a VBA macro:

Sub ImportJSON()
    Dim jsonFile As Integer
    Dim jsonText As String
    Dim jsonObj As Object

    ' Read JSON file
    jsonFile = FreeFile
    Open "C:\data\users.json" For Input As #jsonFile
    jsonText = Input(LOF(jsonFile), jsonFile)
    Close #jsonFile

    ' Parse and write to sheet (requires JSON parser library)
    Set jsonObj = JsonConverter.ParseJson(jsonText)

    Dim i As Integer
    i = 1
    For Each item In jsonObj
        Cells(i, 1) = item("name")
        Cells(i, 2) = item("email")
        Cells(i, 3) = item("age")
        i = i + 1
    Next item
End Sub

⚠ You'll need the VBA-JSON library (JsonConverter) installed. Download it and add it as a module in the VBA editor.

4 Method 4: Python Script

If you prefer scripting, Python makes JSON-to-CSV conversion trivial:

import json
import csv

# Read JSON file
with open('data.json', 'r') as f:
    data = json.load(f)

# Extract all unique keys as headers
headers = []
for item in data:
    for key in item.keys():
        if key not in headers:
            headers.append(key)

# Write CSV
with open('output.csv', 'w', newline='', encoding='utf-8-sig') as f:
    writer = csv.DictWriter(f, fieldnames=headers)
    writer.writeheader()
    writer.writerows(data)

print(f"Converted {len(data)} records to CSV")

💡 Using utf-8-sig encoding adds a BOM header, ensuring Excel correctly detects UTF-8 encoding when opening the CSV.

Or use pandas (even simpler):

import pandas as pd

# One-liner: JSON to CSV
df = pd.read_json('data.json')
df.to_csv('output.csv', index=False, encoding='utf-8-sig')

Tips & Best Practices

  • Always use UTF-8 with BOM — This ensures Excel correctly displays special characters. Use utf-8-sig in Python or our converter (which adds BOM automatically).
  • Flatten nested objects first — Excel works best with flat tables. If your JSON has nested objects, consider expanding them into separate columns before converting.
  • Handle dates carefully — Excel may auto-format date strings. Use Text column format in Excel to preserve the original format.
  • Check for mixed types — If a column has mixed data types (numbers and strings), Excel may not display them correctly. Convert all values to strings first if needed.
  • Large files — For files over 50 MB, consider splitting them into smaller chunks. Excel has a row limit of 1,048,576 rows.

FAQ

Q: How to open a JSON file in Excel directly?

A: Excel cannot open JSON files natively as a spreadsheet. You need to either use Power Query (Data → Get Data → From JSON) or convert the JSON to CSV first using a tool like our converter.

Q: Can Excel import JSON arrays?

A: Yes, using Power Query. Go to Data → Get Data → From File → From JSON. Power Query will parse the array and let you expand it into a table.

Q: Why does Excel show garbled characters after importing CSV?

A: This is an encoding issue. Make sure your CSV file uses UTF-8 with BOM encoding. Our converter adds BOM automatically.

Q: What's the easiest excel json import method?

A: For most users, the easiest method is to convert JSON to CSV using our online tool, then open the CSV in Excel. It takes just 3 clicks: upload, convert, download.

Q: Can I convert JSON to Excel (.xlsx) directly?

A: Yes — convert JSON to CSV first, then open the CSV in Excel and save it as .xlsx. This preserves all formatting and is the most reliable workflow.