How to open JSON files in Excel?
JSON file is generally used to transmit the data on web applications. Most of the time, when the user transmits data on the web, they use JSON file format as it is light weighted. It means that the JSON file contains the data to be transmitted on web.
Besides this, you may also have files that you have created in JSON format. You get a requirement to read all the data at once. You can use MS Excel to open the JSON file for this type of purpose. You will find the JSON file saved with .json extension.
What is JSON and why to use it?
JSON is a standard text-based format that is used for transmitting data in web applications (e.g., client to server or server to client). JSON is based on the JavaScript object syntax. JSON is basically an easy-to-read data structure.
People use JSON because it is very lightweight to work with web applications. So, it can be loaded easily. JSON format is easy to read. Even a non-technical user can understand it. It is written in the form of key-value pair.
This is an example of a JSON file that how it looks like this:
Why to open JSON file in Excel?
If the JSON file contains too much data, it is not easy to read that data at once or comparison of data too. In such scenarios, the JSON users can use MS Excel software for reading and comparing the data. Multiple lines of data can be easily analyzed in Excel.
Excel is a software in which you can insert the row data and read the large set of data that can be seen together. You can compare that too.
- Use MS Excel because its interface is easy to use and utilize by even non-technical users.
- MS Excel can be used as it allows graphical representation of data.
- It is quite good for the financial modeling of data.
The topic discussed in this chapter
We are going to tell the different methods to open and convert the JSON file to Excel/format:
Open JSON into Excel
If you want to open or import the JSON file into Excel, you cannot directly insert into it. You have to follow a process to import the JSON file to Excel. In this chapter, we will instruct you to import the JSON file into Excel.
Excel offers a Power Query feature that can be used to import the JSON file into Excel. Power Query feature is now renamed as Get & Transform tool. It is available inside the Data tab.
Till Excel 2013 version, this feature was named Power Query. Later, it was renamed to Get & Transform in Excel 2016 and later versions. You do not need to write any VBA code script to get the data from the JSON file to Excel.
Example 1
We have created a JSON file containing three employees basic detail. Using this file, we will show you the steps to import the JSON file in Excel. Following the same steps, one can import the JSON data in Excel.
Below is the JSON file that we had created:
Step 1: Now, open the Excel file, either existing or new. Then, navigate to the Data tab.
Step 2: Click the New Query dropdown option and then From File and From Text.
New Query > From File > From Text
In the newer version of Excel, you will explicitly find the From JSON option to import the JSON file. Currently, our MS Excel software has FROM Text option to import the JSON file.
Step 3: Now, navigate to the location where you have the JSON file and also change the file type from Text File to All Files.
You can see that no JSON file is currently showing at this location.
Step 4: Now, select the JSON file and click the Import button to import the file data to the Excel sheet.
Step 5: A Power Query editor will open where click the To Table option.
Step 6: Now, click OK on this panel.
Step 7: One more time, the Power Query editor will load on which click the icon in square box next to the column.
Step 8: Here, you can also unselect the column, you do not want to include in the Excel table. We want to include all columns, so we simply clicked OK without doing anything.
If you forget this step, the data will not load correctly. So, carefully perform all the given steps.
Step 9: You can see that the records have been expanded that the JSON file contains. Now, click the Close & Load option present at the very first place.
Step 10: You can see that the three records have been loaded into the Excel sheet.
You can now compare that which data is more readable.
Step 11: The users can exclude the data rows as well from the sheet. For this, click the dropdown icon next to the column header and then unmark the row you want to hide.
Step 12: See that one record has been hidden for now. However, you can include it again to the table by increasing the width of the table.
You can also follow our chapter to learn how to unhide rows in Excel.
JSON to Excel conversion online
If you have not MS Excel software downloaded, you can do it online as well. Users can use third-party software to open/convert the JSON file to .xlsx format. After converting it, you can open it in MS Excel software.
Various JSON to Excel conversion softwares available online. You can use them for your JSON file conversion. Using these softwares, you can convert your file in a few seconds. But sometimes, these softwares are not reliable because they convert the data wrongly.
JSON to CSV converter
JSON to CSV converter is an online tool for converting the JSON file to CSV and Excel format. Unlike its name, you can also use this software to convert the JSON file to .xlsx format. It is a tool that you can use for free for online file conversion.
Remember that the users require an internet or wifi connection to use this software because it runs on the internet.
Steps to convert the JSON file to .xlsx
Step 1: Click the following link JSON to CSV to go to the website.
Step 2: On this website, click the Upload JSON file to choose a file from your system.
“You can upload up to 1MB of JSON and ZIP file for conversion.”
Step 3: A window panel will open to choose the JSON file from the local storage. Go to the location and select a JSON file to upload.
We have created a JSON file named Employee for the previous example, select it and click Open.
Note: Make sure, All Files must be selected in file type to make the JSON file visible.
Step 4: See that the data has been converted in this way. If you just want to read out the data, you can read it by scrolling the whole data.
But if you want to save it for later, you can download the converted Excel file so that you can read it offline. Three options are available here.
- Download CSV – Click this to download the CSV version of the Excel file.
- Excel File (XLSX) – Choose this option to download the Excel version of the converted JSON file.
- Copy to Clipboard – The user can also go for this option and copy the data to clipboard. After coping the data, you can paste it in simple text format anywhere.
- Do something clever with this result – Apart from all the above options; you can do something different with this converted result.
For example, you can transfer it to Google spreadsheet, SQLite database, MySQL table dump, FTP, Edit, or generate custom reports. But, some of these options are available only for PRO users.
Step 5: Click the or Excel File (XLSX) to download the Excel version of the JSON file.
Step 6: See that a .xlsx file has been downloaded as we had clicked on the Excel file (XLSX) button to download the converted file in Excel format.
Step 20: You can now open and see the converted data in it. Currently, it is in protected view. You can enable editing by clicking the Enable Editing option given in the header of the file.
Apart from this, there are several other softwares on the internet that you can use to convert the JSON file to XLSX format.
JSON to Excel converter
It is another online tool used for converting the JSON file into Excel. It is also a free software. Using this tool, you can convert the JSON file to Excel 97-2007 and Excel 2007+ versions.
The main advantage of using this tool instead of the above one is – it allows maximum of 10 MB of data for conversion. In contrast, JSON to CSV converter allows only 1 MB of data.
Apart from this, this online tool offers 10 conversions per day and also it is easy to use web app. To know how to use this web application for conversion, follow the steps below:
Step 1: Click the following link JSON to Excel conversion to navigate to the website.
Step 2: On this website, click the Browse button to choose a file from your system.
Step 3: Choose a JSON file to convert it into Excel format.
Step 3: We are using Excel 2016, so we choose XLSX (Excel 2007+) radio button here.
Step 4: Now, scroll down and click the Run Conversion to start converting the file.
It will take a little bit of time to convert the file.
Step 5: In the end, click the Download File along with the converted file size to download the file.
Step 6: See that the file has been downloaded. You can now open and see the JSON data in an Excel file.
Note that – this tool does not allow more than one file conversion at a time.
Open the JSON file in Excel from web
There is one more way to open the JSON file directly from the internet without downloading it into your system. Excel enables the method to import/open the JSON file from the web. Thus, the users do not need to explicitly download the file to system and then follow the process to open the file in Excel. This saves the time of users.
The above methods that we have described are for the files that are stored in local storage. If you have a JSON file on the internet or want to import it from an API call, you can follow the below steps:
Step 1: Open a new Excel file and go to the Data tab.
Step 2: click the New Query and select From Other Sources then From Web option that will open a modal to access the file from web.
New Query > From Other Sources > From Web
Step 3: In this modal, choose the Basic radio button and enter the web address of the file inside the URL field, then click OK.
We have provided the following link:
https://filesamples.com/samples/code/json/sample2.json
Step 4: Keep it default without any change and click Connect here.
Step 5: Now, a power query editor with data will open like below on which navigate to the Home tab.
Step 5: In this Home tab, click the Close & Load option present at the very first place.
Step 6: You can see that the six rows have been loaded into the Excel sheet that we had directly imported from the web.
Using this method, one can directly load the JSON file to Excel.
