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

Client database export arrived as single JSON file. Thousands of customer orders. Deadline: end of day. Task: transform into filterable Excel spreadsheet.

Copy-paste failed spectacularly. Online converters crashed on file size. That moment revealed converting JSON to CSV in Excel as essential skill, not optional convenience.

Excel evolved significantly. Where JSON handling once created nightmares, modern versions (especially with Power Query) make structured data conversion straightforward once process understood.

This guide covers practical methods used weekly in production environments. No-code Power Query route for analysts. Automated scripts for power users. 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:

• Data Analysis: You need to run Pivot Tables on a dataset exported from a web app.
• Reporting: Your manager wants a monthly report, and "here is the API endpoint" is not a valid answer.
• Database Migration: You are moving from a document based store to a relational database that only accepts CSV uploads.
• Sanity Checks: You just want to see if the data your developers are sending 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:

Data tab missing or Get Data button absent: verify Excel version includes Power Query (Excel 2016 or newer).

Older versions (Excel 2010/2013): manual Power Query add-in installation may be required.

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:

Power Query method creates persistent "connection."

Source JSON file updated with new data later: no step repetition needed.

Right-click anywhere in Excel table. Select Refresh. Excel re-fetches JSON and updates CSV-like table instantly.

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 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 file type dropdown (default: "All Excel Files") to All Files (.)
4. Select JSON file and click Open

The Catch:

Excel attempts "guessing" hierarchy parsing approach.

Even slightly complex JSON: might dump everything into single cell or fail recognizing 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 Data tab
2. Select Get Data > From Other Sources > From Web
3. Paste JSON data URL (example:

   https://api.example.com/orders

   )
4. Excel performs same Power Query steps mentioned in Method 1

Creates live dashboards where data updates every spreadsheet open. Powerful for real-time reporting environments.

Method 3: Using VBA to Automate the Conversion

If you find yourself needing to convert json to csv in excel dozens of times a week, you might want to automate it. VBA (Visual Basic for Applications) is Excel's internal scripting language.

To do this, you will need a JSON parser for VBA (like the popular VBA-JSON library by Tim Hall). Here is a simplified version of what that logic looks 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
    
**How the script works:** 
1.  **FileSystemObject (FSO):** This part of the code tells Windows to look for a specific file on your hard drive.
2.  **ReadAll:** It loads the entire JSON text into memory.
3.  **JsonConverter:** This is the logic that understands the "keys" and "values" of JSON.
4.  **For Each Item:** It loops through every record in your JSON and writes the specific fields (like "name" and "email") into Excel rows.

**Pros:** One-click conversion for recurring tasks involving hundreds of files.
**Cons:** High setup time; you need to install external libraries and understand basic coding.

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

Excel loves to "helpfully" convert things it thinks are numbers. I once worked on a dataset of product IDs that started with zeros (e.g. **00123**). When I imported the JSON, Excel converted them to **123**, effectively corrupting the data for the database. To fix this, always go into the Power Query Editor and manually 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:
```json
{
  "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

After years of doing this, I have learned a few "pro tips" that will save you hours of troubleshooting:

1. Watch Out for Encoding: Always ensure your JSON is encoded in UTF-8. Excel can be notoriously picky about character encoding. If you see weird symbols where your accented characters should be, open your JSON in a dedicated JSON editor first and convert it to UTF-8 before importing. You can also follow our specific guide on how to format JSON in Notepad++ to make the raw data easier to read before you start.

2. Clean the Data in Power Query: Do not just import and leave. Use the Power Query Editor to:

• Change Data Types: Ensure prices are "Currency" and dates are "Date."
• Remove Nulls: Use "Replace Values" to turn null into a 0 or an empty string.
• Filter: If you only need orders from 2023, filter it before loading it into the spreadsheet. This keeps your Excel file fast and light.

3. Large File Handling: If your JSON file is larger than 1GB, Excel might 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, it is better to use a command-line tool or our JSON File Splitter to "chunk" the data into more manageable pieces before bringing it 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 that the Get & 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 tool like our JSON to Excel Converter or use a Virtual Machine to run the Windows version of Excel.

Other Tools You Might Use

While Excel is my primary tool for analysis, sometimes you just want a quick conversion.

Online Converters: Sites like json-csv.com are great for small, non-sensitive files. If you prefer a desktop approach with a powerful editor, you can also use our JSON to CSV guide for Notepad++ which walks you through plugin-based conversions. Just be careful about uploading private customer data to a site you do not control.

Python: For the tech savvy, a few lines of the pandas library can do this in seconds:

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 on the fly without ever 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:

• Check for Nested Data: Did you expand all the lists and records in Power Query?
• Verify Data Types: Are your dates actually dates and not just text strings?
• Leading Zeros: Check if IDs like 0045 were accidentally turned into 45.
• UTF-8 Encoding: Ensure your special characters (like symbols or accented letters) look correct.
• Row Count: If you have over 1 million rows, remember that CSV is fine, but Excel will only show the first batch!

Wrapping Up

Learning how to convert json to csv in excel used to be a dark art reserved for data engineers. Today, it is a standard office skill. Whether you use the robust Power Query engine, a custom VBA script, or a simple "Format as Table" operation, the goal is always the same: turning raw, machine-readable data into something you can actually use to make decisions.

Next time you are handed a JSON file, do not groan. Open Excel, fire up the Data tab, and start clicking. You will be surprised at how much insight is hidden inside those curly brackets.

Related Guides and Tools:

• JSON to Excel Converter – Our dedicated tool for instant conversion.
• How to Merge JSON Files – For when you have 100 files that need to be one.
• Excel Merger & CSV Merger – Easily combine multiple spreadsheets.
• How to Format JSON in Notepad++ – For making your raw data readable.
• How to Split JSON Files – A guide on breaking down large datasets.
• Best CSV Editors for Windows – Once you have your CSV, here's how to edit it.