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.

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!