How to convert JSON files to Microsoft Excel?

It is well known that we sometimes have data stored in JSON files, but we usually want it to be in the Microsoft Excel sheet as well. It is unnecessary to write the entire data one by one from JSON files to Microsoft Excel when we have several resources to convert the JSON data into Microsoft Excel files.

Besides all this, there are several methods and online software available on the internet using which we can quickly transfer or convert the JSON data to Microsoft Excel effectively.

This chapter will cover all the accessible methods suitable for converting JSON file data to Microsoft Excel. We will also provide the reference of the online JSON to Microsoft Excel conversion tools for free. Moreover, we can also use these practical tools for free, but if we do not have an internet connection available all the time, we can go for our other offline methods respectively.

JSON file

JSON in Microsoft Excel refers to the "JavaScript Object Notation"; it is a lightweight format for storing and exchanging data between the browser and the server. It is a very light weighted file structure that stores data in text format respectively. JSON is a simple text format, but it is challenging to analyze extensive or complete data simultaneously. This is the main issue when the user also needs an Excel file.

Excel file

Microsoft Excel file is primarily a table format structure that is used to store the data in rows as well as in columns. It also stores the data entirely differently than the JSON file. It is a much more understandable and readable format to analyze an extensive data set simultaneously. While in JSON files, it is impossible to read out large amounts of data simultaneously.

What is the fundamental difference between JSON and Microsoft Excel?

It is well known that the respective JSON and Excel are both great ways to store data and are extensively used, but they are also entirely different in structure. It means that they store data in different manners to each other.

Moreover, JSON is a simple text file whose text is structured as a tree, whereas in Microsoft Excel, it is a combination of rows and columns that stores the data in table format. Look at the JSON and Excel files to see how they keep the data.

And most of the time, when we get the data from any external source through API, we usually get the data in JSON format as well.

JSON data representation

Following are the simple text files which are containing data in JSON format efficiently.

This JSON structure contains the three records currently. Now, look for identical records or the data in a Microsoft Excel file that also contains data in table format.

Excel data representation

The Microsoft Excel file contains the data in the table (rows and columns), respectively.

This is the same data as used in the JSON file format. And there is a big difference between the File's data structure and data storage methods.

What to do?

If we have data in a JSON file that we would like to transfer to an Excel sheet, then we can easily do it by just making use of the Power Query. JSON is a data transform format that stores data in simple plain text.

Besides this, we can also use the Get and Transform feature of Microsoft Excel to transfer the JSON data to an Excel spreadsheet effectively. So in this method, we do not need to write the VBA code script, but we can easily import the JSON data to an Excel file in just a few clicks.

Examples

We will elaborate on the steps to convert the JSON data into an Excel file by using the Microsoft Excel built-in Get & Transform feature. It is also beneficial and easy to convert the data from JSON to Excel.

Method 1: Transfer data from JSON to Excel offline

If we have a JSON file that is primarily stored on our computer system, then we can follow the below steps to transfer the data from the JSON file to Excel. It does not require an internet connection. And we need to follow the below steps very carefully simply.

Step 1: We have the employee details data stored in JSON format, a simple text file currently opened in Notepad.

And currently, this File contains three records as well.

Step 2: In this step, we will open the Microsoft Excel file where we want to keep the converted JSON data. We have taken a blank Excel file as well.

Step 3: Now, just after that, we need to go to the Data tab, and then we will click on the Get Query button that is effectively present at the leftmost corner inside the Get & Transform group.

Step 4: In this step, from the dropdown list, we will choose the From File option and then click on the From JSON here.

Note: It is effectively noted that it is a new feature of Microsoft Excel. So, we may not find this option in our Microsoft Excel.

If the From JSON option is not available inside FROM FILE dropdown sub-list, then in that case, we can choose From Text option. It will work the same for our version of MS Excel.

Step 5: After performing the above steps, a panel will open where we need to select our JSON file, which we want to transfer to the Microsoft Excel table. Here, we must select the JSON file and click the Import button.

Step 6: Now, in this step, when we click on the "import button" in the above step, it will then take us to the Excel Power Query Editor.

Here in this, we will encounter the three records that are effectively listed in "Excel Power Query Editor" but cannot see the actual data yet.

Step 7: To make the actual data visible, we will click on the To Table button inside the Transform tab in the power query editor. (This transform tab is automatically opened.)

Step 8: After performing the above steps, a dialog box will open where we need to keep the setting as "default" and click on the OK option.

Step 9: Now our data is in Microsoft Excel table format, but we still cannot see the record details as well, so to expand the column, we are required to click on the Expand Column button efficiently.

Step 10: It has opened a panel that contains the "record headings." And we must mark the respective record heading that we want to include in the Microsoft Excel table and click on the OK option.

Important Tip: We can easily include and exclude the records with their heading in our Excel table while transforming them from JSON files.

Step 11: Now, the data is broken out into the Excel columns along with the records in separate columns, and data is imported to the Excel table and visible too.

"And we can also move the columns and can easily switch from each other where it fits better."

Step 12: Now, to move the column around, we need to right-click on the column header and select the Move button from the list; then, we will choose the direction wherever we want to move it. (left, right, beginning, ending)

Step 13: Once our selected columns get the location where we want to fix, we will navigate to the Home tab and click on the Close & Load button to load the data into the Microsoft Excel data effectively.

It will then load the converted JSON data from Power Query Editor to a Microsoft Excel sheet.

Step 14: In this step, we can easily see that our respective "JSON data" has been imported to the Excel file and is ready to use and perform any Excel operation.

So in this way, we can easily save our time and our effort to manually write down the large set of data stored in the "JSON files." Besides the JSON to Excel, we can also import other Files to Excel, like text, CSV, XML, and more.

Method 2: Convert the Web API JSON data into Excel

Sometimes, we need JSON files that are usually stored in our computer system. And we can directly want to import it to an Excel spreadsheet from the Web API. Web API is termed to be the "Web Application Programming Interface."

In that scenario, instead of From File> From JSON, as in the above step (Step 4), we will choose From Other Source and then From Web, respectively.

A panel will open where you enter the web URL of a JSON file you want to import in Excel.

Convert the JSON data into Excel online.

The method we used above for converting the JSON data to Excel was the built-in feature of Excel. We did not install third-party software or use 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 a hurry and want to put in only a little effort, you can use online software to convert the JSON file to Excel. Several websites offer converters for free with some trials.

The user only requires internet connectivity to upload the 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 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 for only 30 days. After that, you have to purchase a subscription to use it.

Conversion Tool - This online JSON to Excel conversion allows users to convert the JSON data to Excel table format. It offers 10 conversions per day, but you must pay for it if you need more.

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 must 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 use online methods. All these websites provide some free conversion. Besides this, you may find several other free tools for online JSON to Excel conversions.