Convert nested JSON to CSV for Excel

1. Start with an array of records

[
  {
    "id": 1042,
    "name": "Maya",
    "address": {"city": "Boston", "postalCode": "02108"},
    "tags": ["customer","newsletter"]
  },
  {
    "id": 1043,
    "name": "Alex",
    "address": {"city": "London", "postalCode": "SW1A 1AA"},
    "tags": [],
    "active": true
  }
]

Each object in the array becomes one row, so these two give you two rows. If your API wraps the list in something like {"results":[…],"total":2}, copy just the array inside results. The converter won’t dig it out for you.

Notice the postal code is written as a string, "02108". That’s deliberate. As a number, JSON wouldn’t allow the leading zero at all, so codes and IDs like this should always be strings.

2. Check the columns

The nested address turned into two columns, /address/city and /address/postalCode. The column names are paths into the original JSON. If a key itself contains a slash, it shows up as ~1, and a tilde as ~0. Cells marked (empty) are blank in the actual file.

The tags stay together as JSON text in a single cell. A CSV can’t hold a list, and the converter won’t split one record into several rows. If you need one row per tag or per order line, reshape the data before converting. There’s an /active column because Alex’s record has that field, so Maya’s cell in it is empty.

You can untick any columns you don’t need before downloading. null, a missing field, and an empty string all come out as an empty cell, so the CSV can’t tell them apart.

"/id","/name","/address/city","/address/postalCode","/tags","/active"
"1042","Maya","Boston","02108","[""customer"",""newsletter""]",""
"1043","Alex","London","SW1A 1AA","[]","true"

3. Keep leading zeros when you open it in Excel

If you just double-click the CSV, Excel decides each column’s type for you. It sees 02108, decides it’s a number, and shows 2108. Quotes around the value in the CSV don’t stop this. Import the file instead, and tell Excel which columns are text:

1. In Excel, choose Data > From Text/CSV and pick the downloaded file. Check that the delimiter is set to comma.
2. Choose Transform Data and set the /address/postalCode column to Text. If Power Query has already added a Changed Type step, delete or edit it first, or the zeros will be gone before your setting applies.
3. Load the table and check that Maya’s postal code still reads 02108. If it doesn’t, import the CSV again. Formatting the column as text afterwards won’t bring the zero back.

Do the same for any other column of codes, like phone numbers, account numbers, or long order IDs, which Excel can also turn into scientific notation. Menu names vary a little between Excel versions; Microsoft’s page on keeping leading zeros and large numbers covers the other ways to do it.

Before you convert your own data

By default, the converter puts an apostrophe in front of any cell that starts with =, +, -, or @, so Excel won’t run it as a formula. That includes negative numbers, which then come in as text. This example doesn’t have any, but check the option for your own file. It doesn’t stop Excel from dropping leading zeros, so you still need the import steps above.

The converter takes an array of objects up to 2 MB, with up to 10,000 rows and 200 columns, and 500,000 cells in the download. If an object repeats a key, you’ll get an error. If your JSON won’t parse, check or repair it in the validator first.