How to Convert Json to Excel

JSON, short for JavaScript Object Notation, is an open standard file format (text-based) that is used for storing and transporting data. It is commonly used for transmitting data from a server to a web page (e.g., transmitting data from the server to the client so that it can be viewed on a web page or vice versa).

JSON is a data transfer format that comes as a plain text file (like XML). If you have some important data stored in a JSON file that you would like to import into an Excel spreadsheet, you can easily do that with Excel’s ‘Get & Transform’ feature, and you don’t even need any VBA code to import data from the local disk or from a web API. Let’s see how to convert/import a JSON file to an Excel file.

How to Import JSON File into Excel

JSON is represented in a logical, easy-to-read universal data structure. It is made up of only two data types – objects or arrays or a combination of both. Objects are key-value pairs with a colon between them, and arrays are simply collections of objects separated by a comma.

You can easily convert JSON files to Excel files (.xlsx) using Excel’s ‘Get & Transform’ tool (Power Query). Initially, it was called ‘Data Explorer’, then it was renamed to ‘Power Query’. Power Query is only available in 2010 and 2013 versions of Excel. In Microsoft Excel 2016, 2019, and 365, it was again renamed to the ‘Get & Transform’ feature on the Data tab.

This is how a JSON file will look like:

Now, we have a sample JSON file named Employees, as shown above. Let’s see how we can convert this JSON file to an Excel file.

JSON Data

JSON data is written as name/value pairs. A name(key)/value pair consists of a field name (in double quotes), followed by a colon, followed by a value:

"First Name": "Dulce"

JSON Objects

JSON objects can contain multiple name/value pairs (just like in JavaScript), and they are written inside curly braces, as shown below.

{
"First Name": "Dulce",
"Last Name": "Abril",
"Gender": "Female",
"Country": "United States",
"Age": "32",
"Date": "15/10/2017",
"Id": "1562" 
}

JSON Arrays

JSON arrays are written inside square brackets ( [ ] ), and they are collections of objects.

Importing JSON File into Excel

In the example above, the JSON array contains numerous objects. Each object is a record of an employee (with First Name, Last Name, Gender, Country, Age, Date, and ID). In this tutorial, we will use ‘Excel 2016’ to demonstrate the data import.

First, open Microsoft Excel, switch to the ‘Data’ tab, and click the ‘Get Data’ button in the ‘Get and Transform Data’ group at the leftmost corner of the ribbon. From the drop-down, expand ‘From File’ and select ‘From JSON’.

When you click the ‘From JSON’, you will get a file browser window. Find the JSON file on your local disk and click ‘Import’.

If you want to import data from a web API (Web Application Programming Interface), you may want to import the data directly from the internet. To do this, instead of clicking the ‘From JSON’ option, go to the Data tab > Get Data > From Other Source > ‘From Web’ and enter the web URL.

When you click the ‘Import’ button, as shown in the above screenshot, it will bring you to the Excel Power Query Editor. You will notice all the records that were in that list broken into rows. But we can’t see the actual data. To convert this list to a table, click the ‘To Table’ option.

A To Table dialog box will appear. In the dialog, keep the defaults and select ‘OK’.

Now your data is in a table format, but you still can’t see the record details. To expand the columns, click the ‘Expand Column’ button (icon with two arrows pointing away from one another).

You will see the columns that are listed in the records. Select the columns that you like to include in the table and click ‘OK’. Uncheck the columns that you’d like to be excluded.

The data will then be broken out into separate columns as shown below.

You can also move the columns around as you see fit. To do this, right-click on a column header, select ‘Move’, and choose where want to move it.

Once you are satisfied with the layout, click the ‘Close and Load’ button under the ‘Home’ tab to load data into Excel as a Table.

The data will now be imported into a new worksheet in Excel.

Convert JSON File into Excel File Online

If you want quickly convert JSON files to Excel files (.xslx), use one of the many third-party websites available online. They can convert your files in a matter of seconds, but they are not always reliable. Simply type ‘convert JSON to Excel’ in a search engine and you’ll get plenty of websites you can use.

One of the websites you can use to convert JSON to XSLX is json-csv.com. Open the website and click the ‘Upload JSON file’ button to upload the JSON from your local disk. Find the JSON file on your disk and click ‘Open’.