JSON to Excel
Sometimes, you have data stored in JSON files, but you want it to be in Excel sheet. It is unnecessary to write the entire data one by one from JSON file to Excel when you have several resources to convert the JSON data to Excel file. There are several methods and online softwares available on the internet using which you can easily transfer or convert the JSON data to Excel.
In this chapter, we will try to cover all the easy methods to convert the JSON file data to Excel. We will also provide you the reference of online JSON to Excel conversion tools for free. You can use these tools for free, but if you do not have an internet connection available all the time, you can go for our other offline methods.
JSON file
JSON refers to JavaScript Object Notation, a light weighted format for storing and exchanging data between browser and server. It is a very light weighted file structure that stores data in text format. JSON is a simple text format, but it is not easy to analyse the large/complete data at once. This is the main issue when user needs Excel file.
Excel file
Excel file is a table format structure that is used for storing the data in rows and columns. It stores the data in a completely different manner than the JSON file. It is a much understandable and readable format to analyse the large data at once. While in JSON file, it is not possible to read out large data at once.
JSON vs Excel
JSON and Excel both are great ways for storing data and are extensively used, but they are completely different in structure. It means they store data in different manners to each other.
JSON is a simple text file whose text is structured as a tree. Whereas Excel is a combination of rows and columns that stores the data in table format. Look at the JSON and Excel file to see how they keep the data.
Most of the time, when you get the data from any external source through API, you get the data in JSON format.
JSON data representation
Following is the simple text file containing data in JSON format.
This JSON structure is containing three records currently. Now, look for the same records/data in MS Excel file that contains data in table format.
Excel data representation
Following is the Excel file containing data in table (rows and columns).
This is the same data as used in JSON file format. You can see that there is a big difference between both files data structure and way of storing data.
What to do?
If you have data in a JSON file that you would like to transfer in an Excel sheet, you can easily do it by using Power Query. Basically, JSON is a data transform format that stores data in simple plain text.
Besides this, you can also use Get and Transform feature of Excel to transfer the JSON data to an Excel spreadsheet. In this method, you do not even need to write the VBA code script. Just in few clicks, you can easily import the JSON data to an Excel file.
Examples
We will elaborate the steps to convert the JSON data in an Excel file using Excel built-in Get & Transform feature. It is a very helpful and easy method to convert the data from JSON to Excel.
Method 1: Transfer data from JSON to Excel offline
If you have a JSON file stored on your computer system, you can follow the below steps to transfer the data from JSON file to Excel. It does not require an internet connection. Simply follow the below steps-
Step 1: We have the employee details data stored in JSON format, which is a simple text file currently opened in notepad.
Currently, this file contains three records.
Step 2: Now, open the Excel file where you want to keep the converted JSON data. We have taken a blank Excel file.
Step 3: Go to the Data tab and click the Get Query button present at the leftmost corner inside the Get & Transform group.
Step 4: From the dropdown list, choose the From File option and then click the From JSON here.
Note: It is a new feature of Excel. So, it might be possible you do not find this option in your Microsoft Excel.
If the From JSON option is not available inside FROM FILE dropdown sub-list, you can choose From Text option. It will work the same for your version of MS Excel.
Step 5: A panel will open where to select your JSON file, which you want to transfer in the Excel table. Here, select the JSON file and click the Import button.
Step 6: When you click the import button in the above step, it will take you to the Excel Power Query Editor.
Here you will see that three records are listed here in Excel Power Query Editor, but cannot see the actual data yet.
Step 7: To make the actual data visible, click the To Table button inside the Transform tab in the power query editor. (This transform tab is automatically opened.)
Step 8: A dialog box will open where keep the default setting and just click OK.
Step 9: Now your data is in Excel table format, but you still cannot see the record details. To expand the column, click the Expand Column button.
Step 10: It has opened a panel containing the record headings. Mark the respective record heading which you want to include in the Excel table and click OK.
Tip: You can include and exclude the records with their heading in your Excel table while transforming them from JSON files.
Step 11: Now, the data is broken out into Excel columns along with the records in separate columns and data is actually imported to Excel table and visible too.
“You can also move the columns and switch from each other where it fits better.”
Step 12: To move the column around, just right-click the column header and select the Move button from the list, then choose the direction wherever you want to move it. (left, right, beginning, ending)
Step 13: Once your selected columns get the location where you want to fix it, navigate to the Home tab and click the Close & Load button to load the data into Excel data.
It will load the converted JSON data from Power Query Editor to Excel sheet.
Step 14: You can see that your JSON data has been imported to the Excel file and it is ready to use and perform any type of Excel operation.
In this way, you can save your time and effort to manually write the large set of data stored in JSON files. Besides the JSON to Excel, you can also import the other file to Excel, such as text, CSV, XML, and more.
Method 2: Convert the Web API JSON data into Excel
Sometimes, you do not have JSON file stored in your computer system. You directly want to import it to an Excel spreadsheet from the Web API. Web API is a Web Application Programming Interface.
In that scenario, instead of From File > From JSON as in the above step (Step 4), choose From Other Source and then From Web.
A panel will open where enter the web URL of a JSON file you want to import in Excel.
Convert the JSON data into Excel online
The method that we have used above for converting the JSON data to Excel was the built-in feature of Excel. We did not install any third-party software or used online JSON to Excel converter software.
It allows the users to import the JSON file data to an Excel spreadsheet within MS Excel. If you are in hurry and do not want to put a bit of effort, you can use online softwares to convert the JSON file to Excel. Several websites offer converters for free with some trials.
The user only requires internet connectivity to upload JSON file and download the converted file.
Software for JSON to Excel conversion
Following are some references of online JSON to Excel converter –
Safe Software – Safe software is a JSON to Excel converter that comes with 30 days free trial. You can install and use it to convert your JSON file to Excel. The main drawback of this software is that – you can use it for free only 30 days after that you have to purchase the subscription to use it.
Conversion Tool – This is an online JSON to Excel conversion that allows the users to convert the JSON data to Excel table format. It offers 10 conversions per day, but you have to pay for it if you need more conversions.
TextCompare.org – It is another tool to convert the JSON data and load it to an Excel format file. This software allows the users to see the preview before downloading the file in Excel format.
Code Beauty – Code Beauty is an online JSON to Excel conversion tool. It is used to convert the JSON data to table format. You have to upload your JSON formatted data/JSON file URL or JSON file here. It will take a few minutes to convert the file and then you can download the converted file.
If you want to convert the file quickly, you can go for online methods. All these websites provide some free conversion. Besides this, you may find several other free tools for online JSON to Excel conversions.
