JSON to Excel Documentation

Introduction

JSON to Excel is a Microsoft Excel add-in which can convert JSON to Excel.

Requirements

This add-in works in: Excel 2013 Service Pack 1 or later, Excel 2016 for Mac, Excel 2016 or later, Excel Online.

Quick Start

Get add-in

  • Get it from Office Store https://store.office.com/en-001/app.aspx?assetid=WA104381080

  • Click on the above link and, you will see the webpage of JSON to Excel on Office Store.

  • Click on the “Free Trial” button.

  • The next page you will see will show that you can open the add-in in Excel/Excel Online.

  • Now go to Excel 2013/2016 or Excel Online or Office 365.

  • JSON-to-Excel Tab > Launch JSON to Excel

  • Now you are ready to use this add-in.

Use add-in

  • Prepare your JSON data

  • Fill the text area of JSON-to-Excel with your JSON data

  • Select conversion mode , and click on Go button

  1. Simple Mode

Note, for the simple mode, only regular JSON data can be handled. Only the keys in the first element of JSON will be interpreted as header. Apart from the above box, nothing should be worried.

Note: Your JSON shall be wrapped in an array [], see below example. Try to align your format to the below one to avoid errors.

[
    {
        "name":"David",
        "age":20
    },
    {
        "name":"Lily",
        "age":22
    }
]

  1. Nested Mode

Note, for the nested mode, it is currently under test, and you must request for preview before you can use this mode.

Note: Your JSON shall be wrapped in an array [], see below example. Try to align your format to the below one to avoid errors.

Note: The header will come from the first element of the JSON, here in this case, id, detail.name, detail.age, detail.sex, detail.contact.cell, detail.contact.landline will be your header in the Excel file. You can see in the second element (i.e. id = 2), there is an additional company field, but will be ignored by this add-in.

Example JSON

[
    {
        "id":1,
        "detail":{
            "name":"David",
            "age":10,
            "sex":"male",
            "contact":{
                "cell":139,
                "landline":10
            }
        }
    },
    {
        "id":2,
        "detail":{
            "name":"May",
            "age":20,
            "sex":"female",
            "contact":{
                "cell":158,
                "landline":20
            },
            "company":"WTSolutions"
        }
    },
    {
        "id":3,
        "detail":{
            "name":"Harry",
            "age":30,
            "sex":"male",
            "contact":{
                "cell":189,
                "landline":10
            }
        }
    }
]

Errors

Invalid JSON

When the add-in pops up with an error alert of invalid JSON, it means the JSON is not meeting the JSON schema.

Steps to provide a valid JSON

  • Using free webservice for validating JSON, please make sure this website says your JSON file JSON is Valid.

  • Your JSON file shall be wrapped in []

[
    {
        "name":"David",
        "age":20
    },
    {
        "name":"Lily",
        "age":22
    }
]

  • In the above example JSON, you can see there are two element in one Array [], the first element

    {
        "name":"David",
        "age":20
    }

and the second element

    {
        "name":"Lily",
        "age":22
    }

  • Make sure your elements are wrapped in the [].

Too many columns

When you have a pop up of too many columns error, it means you have too many key-value pairs in one single element.

One single element has two key-value pairs,

    {
        "name":"Lily",
        "age":22
    }

and the addin now can accept no more than 26 key-value pairs.

Donation

Your donation is important to WTSolutions Excel-to-JSON add-in, making the server alive to provide you with stable service.

Privacy

This Add-In will not collect and/or transmit User’s JSON data.

Disclaimer

Backup your documents before using this add-in.

About

JSON-to-Excel is an Excel add-in that convert JSON to Excel. It is maintained by he.yang@wtsolutions.cn from WTSolutions.

Feedback

Fill in this form for feedback.