How to Import JSON into Excel

Hi friends! In today’s blog, we’ll explore how to import a JSON file into an Excel table. This process can be incredibly useful for data analysis, reporting, and various other tasks. We’ll break down each step to make it easy for you to follow along.

YouTube player

Working with a Simple JSON File

Step 1: Understanding the JSON File

We’ll start with a simple JSON file. Here is an example of the JSON structure we are using:

{
  "users": [
    {
      "id": 1,
      "name": "John Doe",
      "email": "[email protected]"
    },
    {
      "id": 2,
      "name": "Jane Smith",
      "email": "[email protected]"
    },
    {
      "id": 3,
      "name": "Alice Johnson",
      "email": "[email protected]"
    },
    {
      "id": 4,
      "name": "Bob Brown",
      "email": "[email protected]"
    }
  ]
}

As you can see, this JSON file contains a single property called user, which is an array of objects. Each object represents a user with id, name, and email fields.

Step 2: Importing the JSON File into Excel

To import a JSON file into Excel, you’ll need Excel 2016 or later. This feature is available only in these versions.

  1. Open Excel and Go to the Data Tab
    Start by opening Excel and navigating to the Data tab.
  2. Get Data from File
    Click on Get Data > From File > From JSON.
  3. Locate Your JSON File
    Browse to the location where your JSON file is saved and select it.

This will open the Power Query Editor, which allows us to import and transform data in Excel.

Step 3: Transforming the JSON Data

In the Power Query Editor, you’ll see the structure of your JSON file.

  1. Navigating the JSON Structure
    You’ll notice that the JSON file contains a user property with a list. Click on the List to dig down into the array of items. There are four items in the list, representing each user.
  2. Converting the List to a Table
    Click on the To Table button to convert the list into a table. When prompted, keep the delimiter settings as None and click OK.
  3. Expanding Columns
    After converting to a table, you’ll see a column named Column1 with an expand button. Click on the expand button to show the nested data fields: id, name, and email. Uncheck the “Use original column name as prefix” option to avoid redundant column names and click OK.

You now have a table with columns for id, name, and email.

Step 4: Completing the Import Process

To complete the import process:

  1. Close and Load
    Click on Close & Load in the Power Query Editor. This will load the data into an Excel table where you can further manipulate and analyze it.

Working with a Complex JSON File

Let’s take a look at importing a more complex JSON file. Here’s an example structure:

{
  "projects": [
    {
      "projectId": 1,
      "projectName": "Website Redesign",
      "tasks": [
        {
          "taskId": 101,
          "taskName": "Create Wireframes",
          "subtasks": [
            {
              "subtaskId": 1001,
              "subtaskName": "Sketch Homepage"
            },
            {
              "subtaskId": 1002,
              "subtaskName": "Design Contact Page"
            }
          ]
        },
        {
          "taskId": 102,
          "taskName": "Develop Frontend",
          "subtasks": [
            {
              "subtaskId": 1003,
              "subtaskName": "Setup React Project"
            },
            {
              "subtaskId": 1004,
              "subtaskName": "Implement Responsive Design"
            }
          ]
        }
      ]
    },
    {
      "projectId": 2,
      "projectName": "Mobile App Development",
      "tasks": [
        {
          "taskId": 201,
          "taskName": "Define Requirements",
          "subtasks": [
            {
              "subtaskId": 2001,
              "subtaskName": "Gather User Feedback"
            },
            {
              "subtaskId": 2002,
              "subtaskName": "Analyze Competitor Apps"
            }
          ]
        },
        {
          "taskId": 202,
          "taskName": "Design UI/UX",
          "subtasks": [
            {
              "subtaskId": 2003,
              "subtaskName": "Create Mockups"
            },
            {
              "subtaskId": 2004,
              "subtaskName": "Conduct Usability Testing"
            }
          ]
        }
      ]
    }
  ]
}

1: Importing the Complex JSON File

  1. Repeat Steps for Importing
    Follow the same steps as before to open the JSON file in Excel and navigate to the Power Query Editor.
  2. Navigating the JSON Structure
    The projects property is an array. Select the list and convert it to a table.

2: Handling Nested Arrays

  1. Expanding Nested Arrays
    Click on the expand button for the tasks column. This will further drill down into the task array. You may need to repeat this process if there are multiple levels of nested arrays.
  2. Dealing with Data Duplication
    When expanding nested arrays, there might be duplication of parent rows. For example, expanding tasks for each project will repeat the project details for each task.

3: Completing the Complex Import

  1. Close and Load
    Once you have expanded all necessary fields, click on Close & Load to bring the data into Excel.

Conclusion

Importing JSON data into Excel can seem daunting, but with the right steps, it becomes a straightforward process. Whether you’re working with simple or complex JSON files, Excel’s Power Query Editor provides powerful tools to transform and analyze your data effectively.

Thank you for reading! This is Neil signing off. If you found this guide helpful, please subscribe to my YouTube channel at HowISolve.com and share this with your friends and family. Don’t forget to hit that like button!

Neil Mahaseth

Hello, friends welcome to HowISolve.com, a community founded by me in June 2015. With the aim to help you solve your everyday tech problems. The motivation for starting this community was instilled from my love for technology and my nature to help others in need. By qualification, I am a technocrat with an M.B.A. and B.Tech. from Narsee Monjee, Mumbai. I have experienced both worlds: corporate and startup. I am currently honing my entrepreneurial skills and trying to add value to people's lives. You can also join us by subscribing to our Newsletters & YouTube channel to be a part of this growing community.

View all posts by Neil Mahaseth →