Excel Unlocked

How to Convert JSON to Excel Using Power Query

JSON stands for JavaScript Object Notation. It is a lightweight data-interchange format used to exchange data between a browser and a server in a text file format. This text format is easy to read and write by any conventional C series programmer. Also, it is easy to generate by machines. The JSON file format saves the table data in a specific structure. In this blog, we would learn how to convert or import the JSON file to Excel using the Power Query feature of Excel.

Interestingly, converting a JSON File to Excel using a power query is just a matter of a few mouse clicks. No need of installing any JSON File converter.

Let us first get some insight into what does power query means for excel users.

Introduction About Power Query Excel

Power Query (aka ‘Get & Transform’) is an amazing excel feature used to import data from various external sources (like PDF, Text Files, Websites, and many other sources) into excel. Day by day, Microsoft is adding new data import formats to the Power Query feature.

This feature is pre-installed by Microsoft in the newer excel versions of Excel 2016, 2019, and Office 365.

Are you Excel 2010 or Excel 2013 user? If yes, then check this link.

To learn some basics on Power Query, I recommend you go through the Overview of Power Query Excel Feature.

Sample JSON File Data For Converting To Excel

In the below image you can see how the JSON text file data looks like.

As you can see in the above image, the JSON file contains details about students. Each record is placed within curly brackets, with column headers as – StudentID, FirstName, LastName, ContactNumber, and EmailAddress.

We would use this data to convert the JSON file to an excel table.

I have put the sample JSON file for your practice below. Use the ‘Download‘ button to get this file.

So, let us now begin !!

Step by Step Guide to Convert JSON to Excel

Follow the undermentioned steps to import or extract JSON file format to Excel:

You finally have the JSON data loaded to excel in proper excel table format. See the image below:

With this, we have completed the steps to import JSON data to Excel.

Exit mobile version