Fetch JSON URL Data and Store in Excel using Python

In this article, we will learn how to fetch the JSON data from a URL using Python, parse it, and store it in an Excel file. We will use the Requests library to fetch the JSON data and Pandas to handle the data manipulation and export it to Excel.

Fetch JSON data from URL and store it in an Excel file in Python

The process of fetching JSON data from the URL and storing it in an Excel file can be broken down into three steps:

1. Fetching the JSON Data: We can use the requests library to send the HTTP request to the URL and retrieve the JSON data.
2. Parsing the JSON Data: Once the data is retrieved, it needs to be parsed into a format that can be easily manipulated such as the Python dictionary or list.
3. Storing Data in Excel: We can use the pandas library to create the DataFrame from the parsed JSON data and then export it to the Excel file using the to_excel() method.

Step-by-Step Implementation

Install Required Library:

pip install requests pandas openpyxl

Pandas need openpyxl to interact with the Excel.

Step 1: Import the Required Libraries

First, we need to import the necessary libraries.

import requests
import pandas as pd

Step 2: Fetch the JSON Data

Now, we will use the requests library to fetch the JSON data from the URL. For this example, we will use the placeholder API that returns the JSON data.

url = 'https://jsonplaceholder.typicode.com/todos'
response = requests.get(url)

# Check if the request was successful
if response.status_code == 200:
    json_data = response.json()
else:
    print(f"Failed to fetch data. HTTP Status code: {response.status_code}")

Step 3: Parse the JSON Data

The JSON data can be stored in the json_data variable, which is typically the list of dictionaries. Each dictionary represents a row of data.

Step 4: Convert JSON Data to a DataFrame

We will convert the JSON data into the pandas DataFrame which can be tabular data structure similar to the Excel spreadsheet.

df = pd.DataFrame(json_data)

Step 5: Save the DataFrame to an Excel File

Finally, we will save the DataFrame to the Excel file using the to_excel method. We will specify the file name and the sheet name.

excel_file = 'todos.xlsx'
df.to_excel(excel_file, index=False, sheet_name='Todos')

print(f"Data has been successfully saved to {excel_file}")

Complete Code

import requests
import pandas as pd
# Step 1: Define the URL for the JSON data
url = 'https://jsonplaceholder.typicode.com/todos'
# Step 2: Fetch the JSON data
response = requests.get(url)
# Step 3: Check if the request was successful
if response.status_code == 200:
    json_data = response.json()
    # Step 4: Convert JSON data to a DataFrame
    df = pd.DataFrame(json_data)
    # Step 5: Save the DataFrame to an Excel file
    excel_file = 'todos.xlsx'
    df.to_excel(excel_file, index=False, sheet_name='Todos')
    print(f"Data has been successfully saved to {excel_file}")
else:
    print(f"Failed to fetch data. HTTP Status code: {response.status_code}")

Output

Generated Excel File

Conclusion

In this article, we explored how to fetch the JSON data from the URL and store it in an Excel file using Python. We used the requests library to retrieve the JSON data, pandas to manipulate and structure the data into DataFrame and openpyxl to export the data to the Excel file.

This process can be commonly used in the data analysis, reporting and data integration the tasks, it can allowing you to easily convert the API data into the format that can be shared and analyzed using familiar tools like Excel. With this knowledge, we can now fetch and store the JSON data in the more accessible and structured format.