Convert JSON to CSV in Excel: Step-by-Step Guide (2026)

Use Power Query (Data > Get Data > From File > From JSON) to import and flatten JSON into Excel tables. Excel's Power Query handles nested JSON, arrays, and large files better than manual copy-paste. For recurring conversions, VBA scripts automate the process. For live API data, Power Query can connect directly to JSON URLs.

A common scenario: database or API export arrives as a JSON file with thousands of records, but stakeholders need it in Excel for analysis. Copy-paste isn't realistic for large files. Modern Excel has Power Query built in, although JSON connector availability varies by version and platform.

Practical methods include Power Query for analysts (no coding, handles nested JSON and data cleaning), VBA scripts for automation, URL imports for live API data, and direct open for raw-text inspection only. For instant one-time conversion without opening Excel, use the JSON to Excel Converter tool.

At a Glance: Which Method Should You Use?

Why Convert JSON to CSV in Excel Anyway?

Let us start with 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 editions with the JSON connector include built-in Power Query. It is the best method for converting JSON to CSV in Excel without writing code because you can flatten and clean the data before export.

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: Get & Transform is present in Excel 2016 and later for Windows, but the connectors offered differ by release and license. Microsoft's current matrix lists JSON import for supported newer Windows editions and Microsoft 365 for Mac. The separate Power Query add-in for Excel 2010 and 2013 was deprecated by Microsoft in 2019, so upgrading is safer than building a new workflow around that add-in.

Step 2: Navigate the Power Query Editor

Excel opens new window: Power Query Editor.

Initial appearance may be a detected table or a single row labeled "List" or "Record." A List or Record is normal behavior and represents the top-level JSON structure.

If the preview shows a list, click the To Table button in the Convert section. Click OK in the dialog box. For a record, use the available conversion or expand control.

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.

Repeat the expansion for any remaining Record, List, or Table columns until the fields you need are tabular.

Step 4: Loading and Refreshing

When the columns look satisfactory, click Close & Load in the top-left corner. Excel loads the formatted data into a new worksheet as an Excel table.

Power Query keeps the query and its source connection in the workbook. If the JSON file is updated at the same path, right-click the loaded table and select Refresh, or use Data > Refresh All, to rerun the query. Refresh is manual unless you configure a supported automatic refresh option.

Finally, select File > Save As and choose CSV UTF-8 (Comma delimited) (*.csv) when that option is available. CSV stores only the active worksheet's displayed values, not workbook formatting, multiple sheets, formulas, or the Power Query definition, so keep the Excel workbook if you will need to refresh the source later.

Deep Insight: The Power Query "Case Sensitivity" Trap

The Power Query M language is case-sensitive. If JSON contains the key UserID in one record and userid in another, Power Query treats them as two different columns. Check the resulting headers before loading, and normalize or rename inconsistent fields rather than assuming Excel will merge them.

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

Excel does not list JSON as a supported workbook or text format for structured opening. Even a flat JSON file should be imported through Power Query or converted before it is opened as a spreadsheet.

Process:

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

If Excel rejects the file or displays raw text, that is expected rather than a parsing failure you need to troubleshoot. Microsoft's supported file-format list does not include JSON as a normal File > Open format. Return to Method 1 for a structured table.

New Pro Method: Importing JSON Directly from a URL

A separate download is not required when the JSON is available from a web endpoint. Power Query's Web connector can read the URL and prompt for credentials when the source requires authentication.

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

The query can support recurring reports, but it does not automatically refresh on open by default. Refresh it manually, or follow Microsoft's connection refresh instructions to enable Refresh data when opening the file when that option is supported for the connection.

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
    Dim Item As Variant
    Dim i As Long

    ' 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
    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

This example assumes the top-level JSON value is a collection of objects containing name and email fields. It writes those selected fields to the active worksheet; you still need to save the active sheet as CSV or add carefully reviewed export logic.

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: Repeatable field mapping for a known JSON structure after setup.

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: Power Query capacity depends on available memory, Excel architecture, transformations, and whether the data can be streamed. A worksheet is limited to 1,048,576 rows, according to Microsoft's Power Query limits. Split oversized output with a command-line tool or the JSON File Splitter, or load it to a suitable data model instead of one worksheet.

Troubleshooting Common Issues

"The Get Data option is missing!" Check Microsoft's Power Query source matrix for your exact Excel release, platform, and license. Excel 2010 and 2013 rely on a deprecated add-in, Excel 2016 and 2019 for Mac do not support Power Query, and current Microsoft 365 for Mac releases support JSON import and refresh.

"My data looks like a single line of text." If Power Query shows Record for a file beginning with {, select or convert that record and expand its fields. If it shows List for a file beginning with [, choose To Table and expand the resulting column. Do not wrap valid JSON in extra square brackets merely to change the preview. JSON Lines is a different format and may require preprocessing or a custom Power Query step.

"Excel crashes when I click Expand." Expanding nested lists can multiply the row count, while wide records can create many columns. Select only the fields you need, filter early, close other memory-heavy workbooks, and consider 64-bit Excel or preprocessing when the expanded result approaches Excel's memory or worksheet limits.

A Note for Mac Users

Current Microsoft 365 for Mac supports importing and refreshing JSON through Power Query, while Excel 2016 and Excel 2019 for Mac do not. Microsoft says the Power Query Editor is generally available to Microsoft 365 subscribers running version 16.69 or later.

1. Go to the Data tab.
2. Click Get Data (Power Query).
3. Choose JSON, select the file, and continue to the editor.

If JSON is missing, update Excel and confirm the installed license and version against Microsoft's Power Query availability table linked earlier. Otherwise, use a local conversion tool or another supported Excel installation.

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 flat JSON array can often be converted with a few lines of pandas:

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: A worksheet cannot hold more than 1,048,576 rows. If a CSV exceeds the grid and Excel warns that some data was not loaded, do not overwrite the original because saving can discard the unloaded rows.

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. Power Query, a VBA script, or a simple import workflow can 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.