Save API data into CSV format using Python

In the world of data-driven applications and analysis, APIs (Application Programming Interfaces) play a crucial role in retrieving data from various sources. When working with API data, it is often necessary to store it in a format that is easily accessible and manipulatable. One such format is CSV (Comma Separated Values), which allows tabular data to be organized and stored efficiently. This article will explore the process of saving API data into CSV format using Python.

Let's assume we have an API endpoint that offers data in JSON format. Our objective is to obtain this data and store it as a CSV file for easy manipulation and analysis.

Importing the Required Libraries

The first step involves importing the necessary libraries that will facilitate handling API requests and performing CSV operations. We need two essential libraries: requests for making HTTP requests and csv for working with CSV files.

import requests
import csv

Making the API Request

Let's use a real API endpoint to demonstrate fetching user data. We'll use JSONPlaceholder, a fake REST API for testing and prototyping

import requests
import csv

# Make API request to fetch users data
response = requests.get('https://jsonplaceholder.typicode.com/users')
data = response.json()

print(f"Status Code: {response.status_code}")
print(f"Number of users fetched: {len(data)}")

Status Code: 200
Number of users fetched: 10

Extracting and Preparing the Data

Once we retrieve the JSON data from the API, we extract the relevant information and format it for CSV storage. We'll create a list of dictionaries representing each user

import requests
import csv

# Fetch data from API
response = requests.get('https://jsonplaceholder.typicode.com/users')
data = response.json()

# Extract and prepare data
users = []

for user in data:
    user_info = {
        'Name': user['name'],
        'Email': user['email'],
        'Phone': user['phone'],
        'City': user['address']['city'],
        'Company': user['company']['name']
    }
    users.append(user_info)

print(f"Extracted {len(users)} user records")
print("Sample user:", users[0])

Extracted 10 user records
Sample user: {'Name': 'Leanne Graham', 'Email': 'Sincere@april.biz', 'Phone': '1-770-736-8031 x56442', 'City': 'Gwenborough', 'Company': 'Romaguera-Crona'}

Saving the Data into a CSV File

Now we'll save the extracted data into a CSV file using the csv.DictWriter class

import requests
import csv

# Fetch and prepare data
response = requests.get('https://jsonplaceholder.typicode.com/users')
data = response.json()

users = []
for user in data:
    user_info = {
        'Name': user['name'],
        'Email': user['email'],
        'Phone': user['phone'],
        'City': user['address']['city'],
        'Company': user['company']['name']
    }
    users.append(user_info)

# Save to CSV file
filename = 'users.csv'

with open(filename, 'w', newline='', encoding='utf-8') as csvfile:
    fieldnames = ['Name', 'Email', 'Phone', 'City', 'Company']
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
    
    writer.writeheader()
    writer.writerows(users)

print(f"Data successfully saved to {filename}")

Data successfully saved to users.csv

Verifying the CSV Output

To confirm the successful saving of our data, we can read the CSV file contents and display them

import csv

filename = 'users.csv'

print("CSV file contents:")
print("-" * 50)

with open(filename, 'r', encoding='utf-8') as csvfile:
    reader = csv.reader(csvfile)
    for i, row in enumerate(reader):
        print(f"Row {i}: {row}")
        if i >= 2:  # Show only first 3 rows for brevity
            print("... and more rows")
            break

CSV file contents:
--------------------------------------------------
Row 0: ['Name', 'Email', 'Phone', 'City', 'Company']
Row 1: ['Leanne Graham', 'Sincere@april.biz', '1-770-736-8031 x56442', 'Gwenborough', 'Romaguera-Crona']
Row 2: ['Ervin Howell', 'Shanna@melissa.tv', '010-692-6593 x09125', 'Wisokyburgh', 'Deckow-Crist']
... and more rows

Complete Example

Here's the complete code that fetches API data and saves it to CSV format

import requests
import csv

def save_api_data_to_csv(api_url, filename):
    try:
        # Make API request
        response = requests.get(api_url)
        response.raise_for_status()  # Raise exception for bad status codes
        
        data = response.json()
        
        # Prepare data
        users = []
        for user in data:
            user_info = {
                'Name': user['name'],
                'Email': user['email'],
                'Phone': user['phone'],
                'City': user['address']['city'],
                'Company': user['company']['name']
            }
            users.append(user_info)
        
        # Save to CSV
        with open(filename, 'w', newline='', encoding='utf-8') as csvfile:
            fieldnames = ['Name', 'Email', 'Phone', 'City', 'Company']
            writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
            
            writer.writeheader()
            writer.writerows(users)
        
        return f"Successfully saved {len(users)} records to {filename}"
    
    except requests.exceptions.RequestException as e:
        return f"API request failed: {e}"
    except Exception as e:
        return f"Error: {e}"

# Usage
api_url = 'https://jsonplaceholder.typicode.com/users'
result = save_api_data_to_csv(api_url, 'users_data.csv')
print(result)

Successfully saved 10 records to users_data.csv

Key Points

• Use requests.get() to fetch data from API endpoints
• Handle API responses with response.json() for JSON data
• Use csv.DictWriter for writing dictionary data to CSV files
• Always specify encoding='utf-8' to handle special characters
• Include error handling for robust API data processing

Conclusion

Using Python to save API data into CSV format offers a practical solution for storing and analyzing tabular data. With the requests and csv libraries, you can easily fetch data from APIs, extract necessary information, and organize it into CSV files for further analysis.