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

I still remember the first time I had to deal with a massive JSON export. It was a project for a client who had exported their entire database of customer orders into a single, exceptionally large .json file. My task was to convert that into a clean, filterable Excel sheet by the end of the day. I tried copying and pasting (which was a significant error), and I tried some unreliable online converters that failed due to the file size. That was when I realized that learning how to convert json to csv in excel was not just a "nice to have" skill; it was a necessity for anyone working with modern data.

The good news is that Excel has evolved significantly. While it used to be a nightmare to handle structured data like JSON, modern versions of Excel (especially those with Power Query) make the process surprisingly smooth once you know which buttons to click. In this guide, I am going to walk you through the exact methods I use every week, from the "no code" Power Query route to automated scripts for the power users.

If you are in a rush and just need a quick result without opening Excel at all, you can also use our JSON to Excel Converter for an instant fix.

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)

If you are using Excel 2016 or newer (including Office 365), you have a built-in superpower called Power Query. This is, hands down, the best way to convert JSON to CSV in Excel because it allows you to "flatten" the data without ever writing a single line of code.

Step 1: Get the Data

Open a fresh Excel workbook. Go to the Data tab in the top ribbon.

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

Tip: If you do not see the Data tab or it is missing the Get Data button, ensure you are running a version of Excel that includes Power Query (Excel 2016 or newer). On older versions like 2010/2013, you may need to install the Power Query add-in manually.

Step 2: Navigate the Power Query Editor

Excel will open a new window called the Power Query Editor. At first, it might just look like a single row labeled "List" or "Record." Do not panic; this is just Excel showing you the top level of your JSON structure.

• If you see a button that says To Table in the Convert section, click it.
• A dialog box will appear. Just click OK.

Step 3: Expanding the Columns

This is where the magic happens. You will see a column with a small icon in the header (it looks like two arrows pointing away from each other).

• Click that icon.
• You will see a list of all the "keys" from your JSON (e.g., id, name, email).
• Uncheck Use original column name as prefix (unless you want your column names to be long like Column1.name).
• Click OK.

Suddenly, your hierarchical JSON is transformed into a clean, tabular format.

Step 4: Loading and Refreshing

Once you are happy with how the columns look, click Close & Load in the top-left corner. Excel will dump the formatted data into a new worksheet as a formal Excel Table.

The Power of Refreshing: One of the best things about the Power Query method is that it creates a "connection." If your source JSON file is updated with new data later, you do not need to repeat these steps. Simply right-click anywhere in your Excel table and select Refresh. Excel will re-fetch the JSON and update the CSV-like table instantly.

Finally, go to File > Save As and choose CSV (Comma delimited) (*.csv) to finish the conversion.

Deep Insight: The Power Query "Case Sensitivity" Trap

One thing many guides forget to tell you is that Power Query is case sensitive. If your JSON contains a key like UserID in one record and userid in another, Power Query will treat them as two entirely different columns. As a human who has spent three hours debugging why half my data was "null," I recommend always checking the "Transform" tab to merge or rename columns if your source data is inconsistent.

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

Sometimes, you do not need the heavy lifting of Power Query. If your JSON file is very "flat" (meaning it does not have objects inside objects or nested lists), you can try the direct approach. This is the fastest way but offers the least control.

1. Open Excel.
2. Go to File > Open.
3. Change the file type dropdown (usually says "All Excel Files") to All Files (.).
4. Select your JSON file and click Open.

The Catch: Excel will often try to "guess" how to parse the hierarchy. If the JSON is even slightly complex, it might just dump everything into a single cell or fail to recognize the column headers. I have found this method works about 20% of the time for professional datasets, but it is worth a shot for a quick "look see" before bringing out the bigger tools.

New Pro Method: Importing JSON Directly from a URL

Did you know you do not even need to download the JSON file first? If you are working with a public API or a web endpoint, you can link Excel directly to it.

1. Go to the Data tab.
2. Select Get Data > From Other Sources > From Web.
3. Paste the URL of your JSON data (e.g.,
   Code

   Copy

   https://api.example.com/orders

   ).
4. Excel will perform the same Power Query steps mentioned in Method 1.

This is incredible for creating live dashboards where the data updates every time you open the spreadsheet!

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.