How to Convert JSON to CSV in Excel: A Definitive Step by Step Guide

A common scenario is a database or API export that arrives as a single JSON file with thousands of records, but the people who need to review it want it in Excel. When the deadline is tight, copy and paste is not realistic, and many online converters fail once the file gets large.

Modern versions of Excel have improved a lot here. With Power Query, JSON imports that used to feel painful can be converted into a clean table once you know the right steps.

This guide covers practical options that work in real workflows: a no code Power Query method for analysts, automation paths for recurring tasks, and URL imports for live API data.

Quick result without opening Excel: use JSON to Excel Converter for instant conversion.

At a Glance: Which Method Should You Use?

Why Convert JSON to CSV in Excel Anyway?

Before we dive into the "how," let us talk about the "why." JSON (JavaScript Object Notation) is the language of the web. It is how APIs, databases, and modern apps talk to each other. It is flexible, it handles hierarchies beautifully, and it is relatively lightweight.

However, JSON is not meant for "reading." It is meant for machines. Try looking at a 5,000 line JSON file and finding the average order value; your eyes will cross in minutes. CSV (Comma Separated Values), on the other hand, is the universal language of spreadsheets. Combining the power of Excel with the structured data of JSON gives you the best of both worlds: the richness of developers' data and the analytical power of a spreadsheet.

Common reasons you will need this conversion include:

1. Data analysis: You need to run Pivot Tables on a dataset exported from a web app.
2. Reporting: Your manager wants a monthly report, and "here is the API endpoint" is not a valid answer.
3. Database migration: You are moving from a document based store to a relational database that only accepts CSV uploads.
4. Sanity checks: You want to confirm the data you received is actually correct.

Method 1: The Gold Standard Using Excel Power Query (No Code)

Excel 2016 or newer (including Office 365) includes built-in superpower: Power Query.

Best method for converting JSON to CSV in Excel. Flattens data without writing single code line.

Step 1: Get the Data

Open fresh Excel workbook. Navigate to Data tab in top ribbon.

Click Get Data > From File > From JSON. Select JSON file and click Import.

Compatibility note: If the Data tab is missing or the Get Data button is absent, verify your Excel version includes Power Query (Excel 2016 or newer). On older versions such as Excel 2010 or 2013, you may need to install the Power Query add in manually.

Step 2: Navigate the Power Query Editor

Excel opens new window: Power Query Editor.

Initial appearance: single row labeled "List" or "Record". Normal behavior. Excel displays JSON structure top level.

To Table button visible in Convert section: click it. Dialog box appears. Click OK.

Step 3: Expanding the Columns

Critical transformation step.

Column header displays small icon (two arrows pointing away from each other). Click that icon.

List appears showing all JSON "keys" (example: id, name, email).

Uncheck Use original column name as prefix (prevents long column names like Column1.name). Click OK.

Hierarchical JSON transforms into clean tabular format instantly.

Step 4: Loading and Refreshing

Columns appear satisfactory: click Close & Load in top-left corner.

Excel dumps formatted data into new worksheet as formal Excel Table.

Connection power: The Power Query method creates a persistent connection. If the source JSON file is updated later, you do not need to repeat the steps. Just right click anywhere in the Excel table, select Refresh, and Excel pulls the new JSON and updates the table.

Finally: File > Save As, choose CSV (Comma delimited) (*.csv) to complete conversion.

Deep Insight: The Power Query "Case Sensitivity" Trap

Power Query operates with case sensitivity.

JSON contains key UserID in one record and userid in another: Power Query treats as two entirely different columns.

Three-hour debugging session to discover half data appearing "null": memorable learning experience.

Recommendation: Always check the Transform tab for column merging or renaming requirements when source data shows inconsistency.

Method 2: The Direct "Open" Method (For Simple JSON)

Power Query heavy lifting not always necessary.

Very "flat" JSON file (no objects inside objects or nested lists): direct approach works. Fastest method with minimal control.

Process:

1. Open Excel
2. Navigate to File > Open
3. Change the file type dropdown (default: "All Excel Files") to All Files (.)
4. Select the JSON file and click Open

The catch: Excel attempts to guess the hierarchy parsing approach. Even slightly complex JSON might end up dumped into a single cell, or Excel may fail to recognize column headers.

Success rate approximately 20% for professional datasets. Worth attempting for quick preview before deploying more sophisticated tools.

New Pro Method: Importing JSON Directly from a URL

JSON file download not required. Working with public API or web endpoint: link Excel directly to source.

Process:

1. Navigate to the Data tab
2. Select Get Data > From Other Sources > From Web
3. Paste the JSON data URL (for example, https://api.example.com/orders)
4. Excel runs the same Power Query steps covered in Method 1

This can power live dashboards where the data updates each time the spreadsheet is opened. It is especially useful for recurring reporting.

Method 3: Using VBA to Automate the Conversion

If you need to convert JSON to CSV in Excel repeatedly, automation can be worth it. VBA (Visual Basic for Applications) is Excel's built in scripting language.

To do this, you will need a JSON parser for VBA (for example, the VBA JSON library by Tim Hall). Here is a simplified version of what that logic can look like:

Sub ConvertJSONtoCSV()
    Dim jsonString As String
    Dim jsonObject As Object
    Dim fso As Object
    Dim ts As Object

    ' Read the file
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.OpenTextFile("C:\path\to\your\file.json", 1)
    jsonString = ts.ReadAll
    ts.Close

    ' Parse (Assuming library is installed)
    Set jsonObject = JsonConverter.ParseJson(jsonString)

    ' Write to cells
    Dim i As Integer
    i = 1
    For Each Item In jsonObject
        Cells(i, 1).Value = Item("name")
        Cells(i, 2).Value = Item("email")
        i = i + 1
    Next Item
End Sub

How the script works:

1. FileSystemObject (FSO) tells Windows which file to open.
2. ReadAll loads the JSON text into memory.
3. JsonConverter parses the JSON into objects VBA can work with.
4. For Each Item loops through each record and writes selected fields into Excel rows.

Pros: One click conversion for recurring tasks.

Cons: Higher setup time, plus external libraries and basic scripting knowledge are required.

Deep Insight: Leading Zeros and Excel's "Helper" Habits

Excel can "helpfully" convert things it thinks are numbers. This is a common failure point for IDs that start with zeros (for example, 00123). If Excel imports those as numbers, they may become 123, which breaks matching in databases and downstream systems.

To prevent this, open the Power Query Editor and change the column data type from "Decimal Number" or "Whole Number" to Text before you click Load.

Method 4: Handling Nested JSON (The "Flattener" Problem)

This is the part that trips most people up. JSON often looks like this:

{
  "user": "Imad",
  "purchases": [
    {"item": "Laptop", "price": 1200},
    {"item": "Mouse", "price": 25}
  ]
}

If you convert this directly, what happens to the "purchases"? Do they get their own rows? Do they stay in one cell as a string?

In Power Query, you handle this by Expanding to New Rows.

1. When you see a column containing a "List," click the expand icon.
2. Select Expand to New Rows.
3. You will now have one row for the Laptop and one row for the Mouse, but the "user" name (Imad) will be duplicated for both.

This "denormalization" is exactly what you want for a CSV, as it allows tools like Excel or Google Sheets to accurately perform calculations on the data. If your file is particularly complex or you want to see the flattened version before you even open Excel, our JSON Flattener tool can do this work for you in your browser.

Tips for a Painless Conversion

A few practical tips that prevent the most common problems:

1. Watch out for encoding: Make sure your JSON is saved as UTF 8. If you see strange characters after import, re save the file as UTF 8 before trying again.

2. Clean the data in Power Query: Set the correct data types, replace null values where appropriate, and filter early before loading into the worksheet. This keeps the workbook faster and avoids avoidable errors.

3. Large file handling: If your JSON file is larger than 1 GB, Excel may struggle because it has a hard limit of 1,048,576 rows. If your JSON contains more items than that, you will not be able to load it all into a single sheet. In these cases, split the data into manageable chunks first using a command line tool or our JSON File Splitter before importing into Excel.

Troubleshooting Common Issues

"The Get Data option is missing!" If you do not see the "From JSON" option, you likely have an older version of Excel. You can download the Power Query add in for Excel 2010 and 2013 from Microsoft's website. If you are on a Mac, Power Query support was limited for a long time but is now increasingly available in the latest Microsoft 365 updates.

"My data looks like a single line of text." This happens when the JSON is not formatted as an Array (starting with [). If your file starts with an object {, Power Query might treat it as a single record. You can fix this by clicking the Into Table button or wrapping your entire file content in square brackets [].

"Excel crashes when I click Expand." This usually means your JSON is "deeply" nested—for example, 10+ levels deep. Each expansion requires Excel to recalculate the entire table. Try expanding only the columns you absolutely need instead of selecting "All Columns."

A Note for Mac Users

If you are using Excel for Mac, you might notice the Get and Transform Data (Power Query) experience is slightly different. For a long time, Mac users could not import JSON at all.

However, in recent updates to Microsoft 365 for Mac:

1. Go to the Data tab.
2. Click Get Data (Power Query).
3. You will now see an "Import from JSON" option in many versions. If it is still missing for you, the best workaround is to use an online converter, or use a virtual machine to run the Windows version of Excel.

Other Tools You Might Use

Sometimes you just want a quick conversion without building a full Power Query workflow.

Online converters

Sites like json-csv.com can work for small, non sensitive files. Avoid uploading private customer data to any site you do not control.

Python

If you are comfortable with code, a few lines with the pandas library can convert JSON to CSV quickly:

import pandas as pd
df = pd.read_json('data.json')
df.to_csv('output.csv', index=False)

VS Code

If you are a developer, there are JSON to CSV extensions for VS Code that let you convert data without leaving your editor.

Your Final Checklist for a Perfect CSV

Before you send that file to your manager or upload it to your database, run through this quick checklist:

1. Check for nested data: Did you expand all lists and records in Power Query?
2. Verify data types: Are dates actually dates, and numbers actually numbers?
3. Leading zeros: Confirm IDs like 0045 were not converted to 45.
4. UTF 8 encoding: Make sure special characters and accented letters look correct.
5. Row count: If you have over 1 million rows, remember Excel will only show the first batch even if the CSV has more.

Wrapping Up

Learning how to convert JSON to CSV in Excel used to feel like a specialized data engineering task. Today, it is a practical skill for analysts and developers. Whether you use Power Query, a VBA script, or a simple import workflow, the goal is the same: turn raw, machine readable data into a table you can filter, audit, and use for decisions.

The next time a JSON export lands in your inbox, start with Power Query and work from there. You will often find useful patterns and issues in the data once it is in a spreadsheet view.

Related guide:

1. How to Merge JSON Files if your data is split across many JSON files and you need one combined dataset.