A JSON file looks tidy until Excel opens it as one long, confusing block of text. The brackets, quotes, nested objects, and arrays make it hard to sort, filter, or share with someone who just wants a spreadsheet. The good news: Excel can convert JSON to CSV for free using Power Query, and you do not need a paid add-in or a developer tool.
Below is a practical way to import JSON into Excel, flatten it properly, clean the columns, and export it as a CSV that other apps can read.
Start with the right Excel method
The best free method is Excel’s built-in Power Query importer. It is available in modern desktop versions of Excel, including Microsoft 365 and recent standalone versions. If you are using Excel for the web, the JSON import options are much more limited, so use the desktop app if possible.
Power Query is better than opening the JSON file directly because JSON is not a flat table by nature. It can contain:
A CSV, on the other hand, is a plain table: rows and columns only. Power Query lets you decide how nested JSON should become columns and rows instead of guessing badly.
Before importing, save the JSON file with a `.json` extension. For example:
```text orders.json customers.json api-export-2026-01-15.json ```
If your data is currently copied into a text file, open Notepad or another plain text editor, paste the JSON, and save it as `filename.json`. Avoid saving it as `.txt` unless you have no choice; Excel can still import it, but the JSON connector is easier when the file extension is correct.
Convert JSON to a table in Excel with Power Query
Here is the basic workflow for a normal JSON file that contains a list of records.
Step 1: Import the JSON file
Open Excel and create a blank workbook.
Go to:
```text Data > Get Data > From File > From JSON ```
Choose your `.json` file and click Import.
Excel will open the Power Query Editor. What you see next depends on the structure of the JSON file.
If your JSON starts with square brackets, like this:
```json [ { "id": 1, "name": "Ana", "email": "[email protected]" }, { "id": 2, "name": "Ben", "email": "[email protected]" } ] ```
Power Query will usually show a List. That is normal.
Click To Table in the top-left area. When the dialog appears:
You will now see a column containing records.
Step 2: Expand records into columns
Look for the small expand icon in the column header. It looks like two arrows pointing away from each other.
Click it, then select the fields you want in your CSV. For example:
Uncheck Use original column name as prefix unless you need it. If you leave it checked, Excel may create column names like:
```text Column1.id Column1.name Column1.email ```
That is not wrong, but it is often uglier than needed. For a simple CSV, shorter headers like `id`, `name`, and `email` are easier to work with.
Click OK.
At this point, you should see a normal table: one row per JSON record and one column per selected field.
Step 3: Load the table into Excel
Click:
```text Home > Close & Load ```
Excel will place the transformed data into a worksheet as a formatted table.
Now save the workbook if you want to keep the query for later:
```text File > Save As > Excel Workbook (.xlsx) ```
Keeping the `.xlsx` version is useful because you can refresh the import later if the JSON file changes. The CSV export will not keep the Power Query steps.
Flatten nested JSON without making a mess
The easy case is a flat JSON file. The harder case is nested JSON, and this is where many bad CSV exports happen.
Consider this example:
```json [ { "order_id": 1001, "customer": { "name": "Ana", "email": "[email protected]" }, "items": [ { "sku": "A-10", "qty": 2 }, { "sku": "B-20", "qty": 1 } ] } ] ```
This contains a nested `customer` object and an `items` array. A CSV cannot store that structure in one cell cleanly unless you flatten it.
Expand nested objects into columns
For nested records like `customer`, click the expand icon on the `customer` column.
Select:
This creates columns such as:
```text customer.name customer.email ```
For nested objects, keeping the prefix is often helpful. If you remove it, you may end up with duplicate column names like `name` from both `customer.name` and `sales_rep.name`.
A good practical naming pattern is:
```text order_id customer_name customer_email status created_at ```
If Power Query creates dotted names like `customer.name`, you can rename them by double-clicking the column header.
Expand arrays into rows
Arrays are different. If the `items` column contains a list, expanding it will usually create more rows.
For an order with two items, you will get two CSV rows:
```text order_id,customer_name,customer_email,sku,qty 1001,Ana,[email protected],A-10,2 1001,Ana,[email protected],B-20,1 ```
That is usually the right structure for reporting, filtering, and pivot tables. The order information repeats because each item needs its own row.
In Power Query, click the expand icon or the Expand to New Rows option on the list column. Then expand the resulting records into `sku`, `qty`, `price`, or whatever fields are inside the item records.
Be careful here: if your JSON has multiple arrays at the same level, expanding both can multiply rows unexpectedly. For example, expanding `items` and `payments` together can produce combinations that never existed in the original data. In that case, create separate CSV files:
This is cleaner than forcing everything into one wide CSV.
Clean the data before exporting to CSV
Once your JSON is in Excel, take a few minutes to clean the table before saving it as CSV. This prevents import problems in accounting tools, CRMs, databases, and email platforms.
Set data types deliberately
Power Query often guesses data types. Sometimes it guesses wrong.
In Power Query, check the icon beside each column name:
For IDs, ZIP codes, phone numbers, SKUs, and tracking numbers, use Text, not Number. Otherwise Excel may remove leading zeros or convert long values into scientific notation.
Examples that should usually be text:
```text 001245 02110 +14155550187 SKU-00045 948572938475928374 ```
To change a column type in Power Query:
```text Select column > Transform > Data Type > Text ```
For dates, use Date or Date/Time only if the values are consistent. ISO-style dates like `2026-01-15T09:30:00Z` usually import well. Mixed formats like `01/15/26`, `15 Jan 2026`, and `2026.01.15` can cause errors.
Remove columns you do not need
CSV files are easier to work with when they contain only necessary columns. Remove raw JSON fragments, internal metadata, and duplicate fields before export.
In Power Query:
```text Right-click column > Remove ```
Good columns to remove often include:
```text _links metadata raw_payload debug internal_notes ```
If you need those fields later, keep them in the `.xlsx` workbook but remove them from the final CSV export.
Rename headers for compatibility
Some systems dislike spaces, punctuation, or very long headers. A safe CSV header style is lowercase with underscores:
```text order_id customer_email created_at item_sku item_quantity ```
Avoid headers like:
```text Customer Email Address (Primary) Order #! Item Qty / Units ```
Excel can handle them, but other tools may not.
Check commas, line breaks, and quotes
CSV files can contain commas and quotes inside cells, but they must be escaped properly. Excel usually handles this during export by wrapping affected values in double quotes.
For example, a cell containing:
```text Austin, TX ```
will export as:
```text "Austin, TX" ```
A cell containing a quote, such as:
```text Ana said "approved" ```
will export as:
```text "Ana said ""approved""" ```
That is normal CSV formatting. Do not manually remove those quotes from the CSV file unless you know the receiving system requires something unusual.
Line breaks inside cells are more troublesome. If your JSON includes descriptions, comments, or addresses with line breaks, some older systems may treat one record as multiple rows. If that happens, clean the field in Excel or Power Query by replacing line breaks with spaces.
In Power Query, select the column and use:
```text Transform > Replace Values ```
You can also use a custom step, but for most users it is easier to clean problem fields after loading the table into Excel.
Export the Excel table as a CSV file
Once the data looks right, export it.
Go to:
```text File > Save As ```
Choose a folder, then set Save as type to:
```text CSV UTF-8 (Comma delimited) (*.csv) ```
Use CSV UTF-8 whenever it is available. It handles characters such as accented letters, currency symbols, and non-English names much better than the older plain CSV option.
For example, UTF-8 is safer for values like:
```text José München São Paulo 東京 ```
Excel may warn you that some workbook features are not compatible with CSV. That is expected. CSV saves only the active sheet as plain text. It will not preserve formulas, multiple worksheets, cell colors, filters, Power Query settings, or table formatting.
Before saving as CSV, make sure the worksheet you want is active. If your workbook has several sheets, Excel exports only the current one.
A practical file naming pattern is:
```text orders_clean_2026-01-15.csv customers_export_utf8.csv inventory_flat.csv ```
Avoid vague names like `data.csv` if you will handle multiple exports.
Common problems and how to fix them
Excel shows only one column after import
This usually means the JSON was imported as raw text instead of parsed JSON, or the file is not valid JSON.
Open the file in a plain text editor and check the first character:
If the file contains extra text before or after the JSON, remove it. JSON must start and end cleanly. For example, this is not valid JSON:
```text Here is your export: [{ "id": 1 }] Thanks! ```
It should be only:
```json [{ "id": 1 }] ```
Power Query says there are errors
Click the error cell in Power Query to inspect it. Common causes include inconsistent field types. For example, one record has `"qty": 2`, while another has `"qty": "unknown"`.
If the column contains mixed values, change the type to Text first. You can clean or convert it later.
Long numbers look wrong
Excel may turn long IDs into something like:
```text 9.48573E+17 ```
This is a sign Excel treated the value as a number. Re-import the JSON and set that column to Text in Power Query before loading it.
Do not rely on formatting the column after Excel has already converted the value. Once precision is lost, formatting cannot restore the original digits.
The CSV has strange characters
If names or symbols look broken after export, save again using:
```text CSV UTF-8 (Comma delimited) (*.csv) ```
Also check the app that opens the CSV. Some older programs assume a different encoding unless you explicitly choose UTF-8 during import.
Nested arrays create too many rows
This happens when you expand more than one list column in the same table. Instead of trying to fix the multiplied rows manually, go back to Power Query and create separate exports for each list.
For example:
Use shared IDs, such as `customer_id` or `order_id`, to connect them later.
Excel cannot open a very large JSON file smoothly
For large files, avoid double-clicking the JSON file. Open Excel first, then use:
```text Data > Get Data > From File > From JSON ```
Power Query handles structured imports better than the worksheet grid. Also remove unnecessary columns before loading the data into Excel. If the final table is too large for a worksheet, you may need to split the JSON by date, category, or record type before converting.
What to do if you need the reverse conversion later
Once you have a clean CSV, keep it as your working table. CSV is easier to inspect, filter, edit, and share. If you later need to turn that CSV back into JSON for an API, developer handoff, or app import, use the free CSV to JSON tool. It is especially useful after you have cleaned headers in Excel and want predictable JSON keys like `customer_email` instead of messy spreadsheet labels.
For the Excel conversion itself, the best free route is Power Query: import the JSON, expand records and lists carefully, set IDs as text, clean headers, then export as CSV UTF-8. Take your time with nested arrays; that is where most broken CSV files come from. If your workflow later goes the other direction, try BestAIFinds’ CSV to JSON tool to convert your cleaned spreadsheet data back into structured JSON.