Convert JSON to CSV in Excel with Power Query

Excel’s built-in Power Query can act as a JSON-to-CSV converter: import a JSON file, turn its records into a table, then save the worksheet as CSV. You don’t need a separate online converter. Microsoft documents the JSON import under Data > Get Data > From File > From JSON. Microsoft’s import instructions

The important decision is what each output row represents. For the sample below, the result should be one row per observation, with five ordinary columns—not cells containing nested objects.

These menu steps target Excel for Microsoft 365 on Windows. Power Query is also available in Microsoft 365 for Mac, but menus and connector capabilities differ by platform. Excel version support

1. Start with a small JSON example

Save this invented sample as observations.json in a plain-text editor, using UTF-8 encoding:

[
  {
    "id": "00123",
    "site": {"name": "North, Annex", "city": "Montréal"},
    "observed_date": "2026-10-01",
    "amount": 12.5
  },
  {
    "id": "00124",
    "site": {"name": "South", "city": "Québec"},
    "observed_date": "2026-10-02",
    "amount": null
  }
]

This input deliberately includes a leading-zero identifier, a nested site object, accented characters, a comma inside a name, and a missing amount. Those are useful conversion checks.

Our target columns, in order, are id, site_name, city, observed_date, and amount.

2. Import and flatten the records

1. Open Excel and select Data > Get Data > From File > From JSON.
2. Select observations.json, then Open.
3. Inspect the Power Query Editor preview.

The JSON connector uses automatic table detection to flatten nested data. It may already have expanded the sample into columns; inspect the result before adding transformations. JSON connector documentation

If you still see a top-level List, use Transform > To Table. If the resulting column contains Record values, select its expand icon and choose id, site, observed_date, and amount. Then expand the site record column and select name and city. Rename and arrange the final columns to match the target names and order above.

Microsoft’s structured-column instructions cover To Table and record expansion. An unexpanded record loaded to a worksheet appears as a [Record] placeholder—not the fields you intended to publish. Expand structured columns

For real API responses, the observations may sit inside a field such as data or results. Select the observation collection rather than treating response metadata as dataset rows.

Be deliberate with nested arrays

A nested object such as site becomes additional columns. An array such as "tags": ["urban", "pilot"] needs a different decision:

• Expand to New Rows creates one row per list item.
• Extract Values combines the items into one text value using a chosen delimiter.

Both operations are documented in Microsoft’s list-column guide. Expanding tags would change this example’s row meaning from one observation to one observation–tag pair. For other designs, see flattening lists before CSV export.

3. Set column types before loading

Select each column and use Home > Data Type, or the type icon beside its heading. For this sample, choose:

Column	Power Query type	Reason
id	Text	Preserve 00123 exactly
site_name, city	Text	Keep names as strings
observed_date	Text	Keep the supplied YYYY-MM-DD spelling
amount	Decimal Number	Retain numeric values for analysis

Power Query can infer types automatically, but you can define them explicitly. Change column data types

If the preview already shows 123 instead of 00123, remove or edit the earlier type-conversion step that changed the identifier to a number, then set it to Text. Turning 123 into text will not recreate the lost zeros. Likewise, keep observed_date as Text from before any automatic date conversion if you want its original spelling. Keep null distinct from zero: a missing amount is not a measured amount of 0.

Select Home > Close & Load > Close & Load to load the result to a worksheet. Check for two data rows and five columns. Load a query to Excel

4. Save a UTF-8 CSV and inspect it

First save an .xlsx working copy so you retain the query. Then activate the output worksheet and use File > Save As or Save a Copy. Choose CSV UTF-8 (Comma delimited) (*.csv) and save as observations.csv. UTF-8 export steps

CSV saves only the active worksheet, not the workbook’s other sheets or formatting. Microsoft’s text-export guidance

Open the CSV in a plain-text editor. With comma separators and a decimal point, the intended content is:

id,site_name,city,observed_date,amount
00123,"North, Annex",Montréal,2026-10-01,12.5
00124,South,Québec,2026-10-02,

Check the exact identifiers, accents, quoted comma, dates, and blank amount. Document that a blank amount means missing; CSV does not retain the original JSON structure or a datatype declaration.

If separators are semicolons rather than commas, check the export’s regional settings—Excel’s CSV delimiter and decimal separator can depend on those settings. Delimiter guidance

For a round-trip check, import the saved file through Data > From Text/CSV. In the preview, confirm the encoding and delimiter and turn off automatic data type detection so columns default to Text. Select Transform Data, keep id and observed_date as Text, and set amount to Decimal Number before loading. This avoids losing zeros during the check itself. Text/CSV import options

Follow the CSV export checklist before sharing.

5. Publish only the reviewed table

Remove personal information, credentials, and confidential fields before uploading anything. TablePage accepts CSV and Excel files and turns an upload into a public dataset page with a filterable table and shareable link.

After publication, compare the page’s row count and representative values against the checked CSV—especially 00123, Montréal, and the missing amount. Accompany the dataset with its source, retrieval date, row definition, units, and missing-value meaning so readers can interpret the table without the original JSON.