How to Convert Json to Excel

JSON to Excel

If you have data stored in a JSON file that you would like to convert into an Excel file, you can import it into Excel using Power Query.

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 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 data stored in a JSON file that you would like to import into Excel, it’s now very easy with Excel’s ‘Get & Transform’ feature and it doesn’t require any VBA code to import data locally from the 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 were 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 it is a collection of objects.

Importing JSON File into Excel

In the example above, the JSON array contains numerous objects. And 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 the Microsoft Excel and 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 the ‘From File’ and select the ‘From JSON’ option.

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

If you are using a web API, you may want to import the data directly from the internet. To do this, instead of clicking “From JSON”, click “From Web” and enter the URL.

This should bring you into the Excel Power Query Editor. You will now see 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 our data is in a table format, but we 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) to expand the table.

These are the columns that are listed in the records. Select the columns you’d like to be included in the table and click ‘OK’. Uncheck the columns that you’d like to be excluded.

The data will then be broken out into columns.

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’.

Once you upload the file, you’ll get a preview of your table below. Then, click the ‘Excel File (XLSX)’ button to download your converted Excel file.

That’s it! That’s how you import a .json File in Excel.