How to Import JSON to Excel?

JSON is an abbreviation used for JavaScript Object Notation. It is a way data is often saved or sent. You see it in many places, like when computer programs talk to each other or in settings files. But Excel doesn’t easily open JSON files like it does regular tables.

This article will show you different ways to get your JSON data into Excel spreadsheets. We will cover:

• Using Excel (Power Query).
• Using an online tool.
• Using a little bit of programming (Python).

Method 1: Using a Tool Inside Excel (Power Query)

Excel has a tool called Power Query that can help.

Here are the simple steps:

1. Open a blank Excel file.
2. Go to the “Data” tab at the top.

3. Find “Get Data,” then choose “From File,” and then “From JSON.”
4. Find your JSON file on your computer and open it.
5. Power Query will show you the data. You will need to click on parts of the data to make it look like a table. You need to click the little expand buttons to see all the information, especially if the data is nested inside other data.
6. You can change the names of the columns here if you want.
7. Click “Close & Load.” Your data will appear in an Excel sheet.

This method is already in Excel, which is good. If your JSON data changes often, Power Query can refresh the data.

However, it can be tricky if your JSON has many levels of nested data. You have to manually expand things, which takes time. It also doesn’t easily put lists of items into separate rows if the list is deep inside the data. You also don’t have many choices for how the final table looks.

This method works on Excel 2016 and newer versions on Windows. You need to learn how to use the Power Query window a bit.

How can you import JSON files in Office 2010 version? 

• Open a New Excel Workbook and look for the Power Query tab. 
• In the Power Query tab, go to Other sources and then click Blank Query. 
• Now, go to the JSON file on your computer and copy the path of the file. 
• In MS Excel, it will give you a blank space for your query, there you need to paste the path copied from the JSON file. 
• Click OK and it will load the JSON file in Excel Power Query. Now you can manipulate the data of the JSON file. 

The version of MS Office introduced after Office 2010 was 2013. There were some changes in this version and let’s see how you can import your JSON files in Excel 2013.

• Open a New Excel Workbook and find the Power Query tab. 
• Go to Other sources and then choose a blank query.
• Go to the Advanced editor in the Query Editor.
• Enter the query as the path of your JSON file.

Method 2: Using an Online Tool

An online tool can make this much easier, especially if you don’t want to deal with Excel’s built-in tools or programming.

Why use an online tool?

• You don’t need to know Power Query or coding.
• It can handle JSON data that is complex or nested on its own.
• You can often choose how the final file looks and which data fields to include.

Here is how to use an online tool:

1. Go to the JSON to Excel Converter tool.
2. You can copy and paste your JSON data, upload a JSON file from your computer, or even give it a web address where the JSON is.
3. The tool will automatically work with the nested parts and lists in your JSON data.
4. You might be able to drag and drop to put the columns in the order you want.
5. Choose what kind of file you want (like .xlsx for newer Excel, .xls for older Excel, or .csv, which is a simple text file).
6. You can set the file name, and if you wan,t a row at the top with names for the columns.
7. Click a button to convert. You might see a bar showing how far along it is.
8. Download the new Excel file.

Key things about this online tool:

• It works on phones or tablets, too.
• It can handle single JSON items or lists of items automatically.

Method 3: Using Python (If You Like Coding)

If you are comfortable with a little bit of coding, Python is a very flexible way to convert JSON to Excel.

You need to have Python installed on your computer. You also need a Python library called Pandas, which is good with data tables.

Here is a basic script:

import pandas as pd
import json

# Open your JSON file and load the data
with open('your_data.json') as f:
    data = json.load(f)

# Turn the JSON data into a table format (DataFrame)
# json_normalize is good for handling nested data
df = pd.json_normalize(data)

# Save the table data into an Excel file
# index=False means don't write the row numbers from Pandas
df.to_excel('your_output.xlsx', index=False)

This script does a few things: It opens your JSON file, reads the data, uses a special function (json_normalize) from Pandas to flatten the data into a table, and then saves that table into an Excel file.

If your JSON is more complex, you can use more advanced options in the json_normalize function to handle nested lists or objects exactly how you want. You can also write Python code to read many JSON files at once and combine them into one Excel file.

Comparing the Methods

Here is a simple table to compare the three ways:

Which Method Should You Use?

The best method depends on what you need:

• If you want something quick and simple, especially for complex JSON, use the online tool.
• If you need to do many custom things, combine lots of files, or repeat the process automatically, using Python gives you the most power.
• If your JSON is simple and you are already working in Excel (version 2016 or newer), using Power Query is a built-in option.

Choose the method that fits best with your skills and what you need to do with your data.