As you all know Microsoft Excel is a very powerful tool as it is not just an application to run a spreadsheet, it is a lot more capable than that.

For instance, if you have your data stored in a JSON file, then you can simply convert it and open it on Excel. You can import your JSON file to Excel using the Power Query.

JSON stands for JavaScript Object Notation it is an open standard file format that can be used for storing and transporting different kinds of data. Mostly it is used for transporting data from a server to a web page.

JSON is a data transfer format that is a plat text file like every other XML file. So, if you have any important data or information that is stored in a JSON file you can import it and open it on a Microsoft Excel spreadsheet.

Excel provides a feature called as ‘Get and Transform’ feature that can be used to import the file and you won’t be required any kind of VBA code to import data from the local disk or a web API.

Below we have an extensive guide on how you can use Microsoft Excel to import JSON file and open it on the spreadsheet to fetch your information or edit it.

How To Import JSON File into Excel

JSON is used universally for a data structure that is logical as well as easy to read data. This is because it is made up of only two different data types and they are objects and arrays or a combination of both. Usually in a JSON file objects are the key-value pairs with a colon between them and then some arrays are simply the collection of objects. These arrays are separated by commas.

Initially, the ‘Get and Transform’ tool from Microsoft Excel was called ‘Data Explorer’ but with recent changes in Microsoft Office, now it is called ‘get and Transform'(Power Query). Using the recent versions of Microsoft Excel, you can easily convert your JSON files to Excel files (.xlsx) using the ‘Get and transform’ tool. Do note that the Power Query tool is only available in 2010 and 2013 versions of Excel. Starting from 2016 and onwards the feature was renamed to ‘Get and Transform’ and is located in the Data tab.

Below we have a sample of how a JSON file usually looks you can go through it for better understanding.

Now that we have seen a sample of the JSON file, we will further use the same file to see how we can convert the JSON file to an Excel file, you can use your sample or the original JSON file that you want to convert to Excel.

JSON Data

The JSON data is written in name/value pairs. A name(key)/ value pair has a field name ( in double-quotes) and is followed by a colon which is followed by a value.

"First Name": "Dulce"

JSON Objects

In a JSON file, the JSON objects can have multiple different names/pairs (just like in JavaScript) and they are written inside curly braces. Below we have a sample for the same.

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

JSON Arrays

Usually, JSON arrays are written inside square brackets ({}) and it is a collection of objects.

Importing JSON File into Excel

The JSON array is capable of storing a lot of objects. Each object is a record of information in our case each object stores records of an employee like their First Name, Last name, gender, Country, Age and ID. We will be using Excel 2016 to demonstrate how to import data from JSON to Excel.

1. First, you need to open the Microsoft Excel 2016 on your computer and then switch to the Data tab from the top ribbon bar and then you can click on the Get Data button inside the ‘get and Transform Data’ group which should be located at the leftmost corner of the ribbon.
   
2. From the drop-down, you can expand the option that says ‘From File’ and then select the ‘From JSON’ option.
3. Once you click on ‘From JSON’, you will get an option to navigate through the file explorer and choose a file, find your JSON file on your local disk that you want to convert and then click on Import.
   
4. Otherwise, if you want to import your data from a web API then you may want to import data directly from the internet.
5. To import data directly from the internet, instead of clicking the ‘From JSON’ you need to go to the data tab >> Get Data>> From Other Source>> From Web and then enter the web URL from where you want to import.
6. Once you click on the Import button it should bring you into the Excel Power Query Editor. YOu will also be able to see all of the records that were in the list broken into different rows. However, we won’t be able to see the actual data just yet. We need to convert this list to a table, so click on the ‘To Table’ option.
   
7. A TO Table dialogue box should appear. From that dialogue box, keep the default setting and then click on OK.
8. Now your data is in a table format, but you still won’t be able to see the record details. To expand to columns and see the information then you need the ‘Expand Column’ button (icon with two arrows pointing away from one another).
9. You will see all the columns that are listed in the records. Now, you can select the columns that you like to include in the table and then click OK. Also, uncheck the columns that you want to exclude from the table.
10. The data should be broken out into separate columns as shown below.
11. Excel also allows you to move the columns around as you see fit. In order to move around, you need to right-click on a column header, then select Move and then choose wherever you want to move it.
12. Once you are done editing the layout and is satisfied you can click the ‘Close and Load’ button located under the Home tab to load data into Excel as a table.
    
13. You will be able to see the data that is imported into a new worksheet in Excel.

Convert JSON File into Excel File Online

If you find the aforementioned steps a bit complex or it is messy, then you can also use this method to quickly convert your JSON files into Excel files(.xlsx) by using one of the many third-party websites available online. These websites can easily convert your files only in a few seconds, but they aren’t always reliable so make sure to check if it converts correctly. Use any of the search engines on your browser and search convert JSON to Excel and you shall see plenty of websites in the result that can be used to convert your JSON file to an Excel file.

We suggest you can use JSON-csv.com website to convert your JSON file into an Excel file. All you need to do is upload your JSON file from your local disk. Find the JSON file on your disk and then click Open.

Once you have uploaded the file, you will also get a preview of your table below. You need to click on the Excel File button to download your converted Excel file.

Verdict

This was an extensive guide on how you can convert your JSON file into an Excel file and then use it to edit or view the data that you want to check. We recommend that you can always try the website method to convert your JSON file to Excel, if it doesn’t work then you can do so manually by following the above tutorial.