How to import JSON data into Excel ?

How to import JSON data into Excel ?

Introduction

With the popularity of RESTful APIs JSON has become a popular format to store data.
We get JSON data as a response from the APIs and if we need to send the data to someone who can understand the data better we need to convert import the data from JSON file into excel.

In this post we will see how we can import the json data into excel. We would be using a popular api for fake json data in our demo.

API Endpoint to get posts

Creating a JSON file from API

For this demo we would be using jsonplaceholder api for generating a fake json data containing blog posts.

Click on the api endpoint in the browser.
Right click and save the file as JSON type.

Importing JSON into Excel

Let us now follow along with the steps mentioned to import the JSON data.

  1. Open a new Excel file. Press Windows+R on windows to open the "Run" window. Type excel and press enter.
  2. Click on the Data tab.
  3. Click on the first menu icon Get Data and then select the option From File and then From JSON.
    Importing JSON into Excel
  4. Select the JSON file and click on OK. Importing JSON into Excel
  5. Click on the To Table present on the Convert option at the beginning of the excel menu.
    Importing JSON into Excel
  6. Click Ok and you would see the screen similar to the one mentioned below.
    Importing JSON into Excel
  7. Click on the icon with two arrows in the screen above and then click Ok.
    You would notice that the data from the excel is now copied into the excel.
    Importing JSON into Excel

In this step you can even select the json property that needs to be displayed in the excel.

  1. Click on Close & Load option to load the data into the excel.
    Importing JSON into Excel

I hope you would find this post useful when trying to upload data into the excel.