JSON is used to transfer the data between the server and web applications and it is mostly used by programmers. It uses key value pairs to store the information. There are many advantages of JSON like it is used to work with complex data, share the data, is faster, etc. But it is mostly understood only by programmers or the person who works on it. If a common man needs to understand the data present in it then it would be a little difficult task for them. This can be made easy if we convert the JSON data into excel since excel data is easily understood by everyone and easily readable. Excel has a variety of options to perform different tasks, and one of those is Transform the data. In this article let us see how to convert the JSON data into excel.
Convert Json Data Into Excel
This process is pretty much easier since excel has an option to convert and load the data. It is a step-by-step process that involves importing the JSON file, manipulating the format as per your need, filtering the data if necessary, converting it, and transforming it into excel. All these are done with the help of Power Query Editor and finally, your data is in the excel format now. Let us look deeply into this process.
Before jumping into Step 1 here is the preview of my JSON data which we need to convert into excel and it is been saved as a test.json file.
Step 1: Open a new excel, and click on the Data tab present in the Excel ribbon. There appear many options under the data tab.
Step 2: In the data tab click on the Get Data option which has a dropdown and there appears a list of options. Click on From File and again in the list appeared click on From JSON option. This means you want to import the data from the JSON file into excel.
Step 3: There comes a window where you need to browse and navigate to the location where the JSON file is present. Click on it and open it.
Step 4: Now a Power Query Editor window will open where we can perform some operations like filter, move, sort, etc. Click on the Into Table option present in the left corner of the window. This will convert the data into a Table.
Step 5: You must now see the table in the window, but you may wonder why there is no data on the appeared screen, so just click on the option which has two arrows pointing away from each other to expand the cells. You will get two options, click on Expand to New Rows.
Step 6: Again click on the two arrows pointing away from each other options. There appears a list of columns, you can select the columns which need to be in the excel by clicking on the checkbox. You can also uncheck the checkbox if you do not need that particular column. Click on OK.
Step 7: Now the data is ready and you can see it in the window. If at all you need to do some modifications like moving the column to right, left, etc you can right-click on the header of the column, there appears a list of options. Click on the Move option and choose the alignment you need.
Step 8: When you are satisfied with the layout, click on the Close & Load option present in the top left corner. This will load the data from the Power Query Editor to the excel sheet.
Step 9: Now the excel is loaded with the data and it looks like the below. You can also see the message that how many rows are loaded into the excel from the right corner under Queries. This way JSON data is converted into excel and is easily understood and readable.
This was the manual process of converting a JSON file into excel. But there is also a quicker option where you can convert the file online. There are many websites that provide this option where in you just need to upload your JSON file and click on the convert button that’s it, within seconds it will convert and give you back the data in excel. You can find these websites very easily on a single web search. So if you do not prefer manual conversion go for online conversion. Saying so, it is always good to know what are the steps involved in the manual process and how to do it by yourself.
That’s it for this article. Hope the information was useful and you were able to do it easily. Thank you and Happy Reading !!!