csvjson
·11 min read

How to Convert API Response JSON to CSV (Stripe, GitHub, Shopify, and More)

Real-world guide to converting API response JSON to CSV and Excel. Uses actual Stripe, GitHub, Shopify, and Twitter API response structures — not toy examples. Covers Python, online tools, and the specific nesting patterns each API uses.

On this page

You've made an API call. You have the response — a wall of JSON with nested objects, arrays of arrays, and fields that only exist on some records. Now your manager wants it in a spreadsheet by 2pm.

Most tutorials show you how to convert [{"name": "Alice"}, {"name": "Bob"}]. Real APIs don't look like that. Stripe wraps everything in a data envelope. GitHub paginates across hundreds of pages. Shopify nests line items three levels deep. Twitter's v2 API separates users from tweets in a includes object that requires a join.

This guide uses actual API response structures — copied from the documentation and production responses — and shows you how to get each one into a flat CSV or Excel file.

The core problem: APIs return hierarchical data, CSVs are flat

Every API response you'll encounter falls into one of these shapes:

Shape 1 — Envelope with a data array (Stripe, most REST APIs)

{
  "object": "list",
  "data": [ {...}, {...}, {...} ],
  "has_more": true,
  "total_count": 843
}

The records you want are inside data. The top-level keys are metadata.

Shape 2 — Direct array (GitHub, simple REST endpoints)

[
  {"id": 1, "login": "alice", ...},
  {"id": 2, "login": "bob", ...}
]

Clean and simple. pandas reads this in one line.

Shape 3 — Records with nested sub-arrays (Shopify orders, Stripe invoices)

[
  {
    "order_id": "5001",
    "line_items": [
      {"sku": "SHOE-BLK-42", "quantity": 1, "price": 89.99},
      {"sku": "SOCK-GRY-M",  "quantity": 3, "price": 7.99}
    ]
  }
]

Each record contains an array of sub-records. You have to decide: one row per order, or one row per line item?

Shape 4 — Side-loaded related objects (Twitter v2, JSON:API)

{
  "data": [ {"id": "1234", "author_id": "567", "text": "..."} ],
  "includes": {
    "users": [ {"id": "567", "name": "Alice", "username": "alice"} ]
  }
}

The main records reference related objects by ID. Getting a flat CSV requires a join.

The right conversion strategy depends on which shape you're dealing with. The sections below cover each major API.

Stripe API

Stripe is the hardest common case. Payments, customers, invoices, and subscriptions all have multiple nesting levels and optional fields that vary by payment method.

What a Stripe payment intent actually looks like

{
  "id": "pi_3MtwBwLkdIwHu7ix28a3tqPD",
  "object": "payment_intent",
  "amount": 2000,
  "currency": "usd",
  "status": "succeeded",
  "created": 1680729600,
  "customer": "cus_NffrFeUfNV2Hib",
  "payment_method_details": {
    "card": {
      "brand": "visa",
      "last4": "4242",
      "exp_month": 8,
      "exp_year": 2026,
      "country": "US"
    },
    "type": "card"
  },
  "metadata": {
    "order_id": "6735",
    "campaign": "summer_sale"
  },
  "charges": {
    "object": "list",
    "data": [
      {
        "id": "ch_3MtwBw2eZvKYlo2C0XjbNTen",
        "amount": 2000,
        "receipt_url": "https://pay.stripe.com/receipts/..."
      }
    ]
  }
}

The real Stripe response has 40+ fields. This is the important structure: payment_method_details is a nested object, metadata is a nested object, and charges is another envelope-wrapped list.

Fetching and converting Stripe payments

import stripe
import pandas as pd
import json

stripe.api_key = "sk_live_..."

# Fetch all payments (handles pagination automatically)
payments = []
page = stripe.PaymentIntent.list(limit=100)
while True:
    payments.extend(page.data)
    if not page.has_more:
        break
    page = stripe.PaymentIntent.list(limit=100, starting_after=payments[-1].id)

# Convert to dicts for json_normalize
records = [p.to_dict() for p in payments]

# Flatten — stop at level 2 to avoid exploding the charges sub-list
df = pd.json_normalize(records, sep=".", errors="ignore", max_level=2)

# Keep and rename the columns you care about
column_map = {
    "id":                                   "payment_id",
    "amount":                               "amount_cents",
    "currency":                             "currency",
    "status":                               "status",
    "created":                              "created_unix",
    "customer":                             "customer_id",
    "payment_method_details.card.brand":    "card_brand",
    "payment_method_details.card.last4":    "card_last4",
    "payment_method_details.card.country":  "card_country",
    "metadata.order_id":                    "order_id",
}

df = df.rename(columns=column_map)
df = df[[c for c in column_map.values() if c in df.columns]]

# Convert amount from cents to dollars
df["amount_usd"] = df["amount_cents"] / 100

# Convert unix timestamp
df["created_at"] = pd.to_datetime(df["created_unix"], unit="s").dt.strftime("%Y-%m-%d %H:%M UTC")
df = df.drop(columns=["amount_cents", "created_unix"])

df.to_csv("stripe_payments.csv", index=False, encoding="utf-8-sig")
print(f"Exported {len(df)} payments")

No Python? Paste the raw JSON response into the JSON to CSV converter. It flattens payment_method_details.card.brand and metadata.order_id into proper column names automatically. Use it for one-off exports; use the script above for recurring pulls.

GitHub API

GitHub's REST API returns clean arrays — the easiest shape to work with. The challenge is pagination (GitHub caps at 100 results per page) and rate limits (60 requests/hour unauthenticated, 5,000 with a token).

Issues export

[
  {
    "id": 1,
    "number": 142,
    "title": "Fix CSV export encoding on Windows",
    "state": "open",
    "created_at": "2024-01-15T10:22:00Z",
    "closed_at": null,
    "user": {
      "login": "alice",
      "id": 1001,
      "type": "User"
    },
    "labels": [
      {"id": 101, "name": "bug", "color": "d73a4a"},
      {"id": 102, "name": "good first issue", "color": "7057ff"}
    ],
    "comments": 3,
    "assignees": [
      {"login": "bob", "id": 1002}
    ]
  }
]

user is a nested object. labels and assignees are arrays of objects. closed_at is null on open issues.

import requests
import pandas as pd

REPO = "owner/repo"
TOKEN = "ghp_..."  # optional but recommended

headers = {"Authorization": f"token {TOKEN}"} if TOKEN else {}

issues = []
page = 1
while True:
    resp = requests.get(
        f"https://api.github.com/repos/{REPO}/issues",
        params={"state": "all", "per_page": 100, "page": page},
        headers=headers,
    )
    resp.raise_for_status()
    batch = resp.json()
    if not batch:
        break
    issues.extend(batch)
    page += 1
    # Respect rate limit headers
    if int(resp.headers.get("X-RateLimit-Remaining", 1)) == 0:
        print("Rate limit hit — wait 60s or add a token")
        break

df = pd.json_normalize(issues, sep=".")

# Arrays of objects — join to readable strings
df["labels"] = df["labels"].apply(
    lambda x: ", ".join(l["name"] for l in x) if isinstance(x, list) else ""
)
df["assignees"] = df["assignees"].apply(
    lambda x: ", ".join(a["login"] for a in x) if isinstance(x, list) else ""
)

# Keep useful columns
keep = ["number", "title", "state", "created_at", "closed_at",
        "user.login", "labels", "assignees", "comments"]
df = df[[c for c in keep if c in df.columns]]
df = df.rename(columns={"user.login": "author"})

df.to_csv(f"{REPO.replace('/', '_')}_issues.csv", index=False)
print(f"Exported {len(df)} issues")

For a quick export without writing code, fetch the first page of results in your browser at https://api.github.com/repos/owner/repo/issues?per_page=100, copy the JSON, and paste it into the JSON to CSV converter.

Repository stars / contributors export

The same pattern applies for other GitHub endpoints. Contributors endpoint returns a direct array:

resp = requests.get(f"https://api.github.com/repos/{REPO}/contributors", headers=headers)
df = pd.DataFrame(resp.json())[["login", "contributions", "type"]]
df.to_csv("contributors.csv", index=False)

Shopify API

Shopify orders are the canonical example of Shape 3: records with nested sub-arrays. An order has multiple line items, each with its own SKU, quantity, and pricing. How you flatten this depends on what you're trying to analyze.

What a Shopify order actually looks like

{
  "id": 820982911946154500,
  "order_number": 1001,
  "email": "bob.norman@example.com",
  "created_at": "2024-11-15T12:00:00-05:00",
  "total_price": "199.98",
  "financial_status": "paid",
  "fulfillment_status": "fulfilled",
  "customer": {
    "id": 207119551,
    "first_name": "Bob",
    "last_name": "Norman",
    "email": "bob.norman@example.com"
  },
  "shipping_address": {
    "city": "Chestnut Hill",
    "province": "Massachusetts",
    "country": "United States",
    "zip": "02467"
  },
  "line_items": [
    {
      "id": 866550311766439000,
      "title": "IPod Nano - 8GB",
      "sku": "IPOD2008PINK",
      "quantity": 1,
      "price": "199.00",
      "vendor": "Apple",
      "variant_title": "Pink"
    }
  ],
  "tags": "returning-customer, vip"
}

Option A: One row per order (order-level analysis)

import pandas as pd
import json

with open("shopify_orders.json") as f:
    orders = json.load(f)

df = pd.json_normalize(orders, sep=".", errors="ignore", max_level=1)

# Summarize line items instead of exploding them
df["item_count"] = df["line_items"].apply(
    lambda x: sum(item["quantity"] for item in x) if isinstance(x, list) else 0
)
df["skus"] = df["line_items"].apply(
    lambda x: "|".join(item.get("sku", "") for item in x) if isinstance(x, list) else ""
)
df = df.drop(columns=["line_items"])

keep = ["order_number", "created_at", "total_price", "financial_status",
        "fulfillment_status", "customer.email", "shipping_address.city",
        "shipping_address.province", "item_count", "skus", "tags"]
df = df[[c for c in keep if c in df.columns]]

df.to_csv("shopify_orders.csv", index=False)

Option B: One row per line item (product-level analysis)

df_items = pd.json_normalize(
    orders,
    record_path="line_items",
    meta=[
        "order_number",
        "created_at",
        "total_price",
        "financial_status",
        ["customer", "email"],
    ],
    sep=".",
    errors="ignore",
)

df_items.to_csv("shopify_line_items.csv", index=False)

This produces one row per purchased item, with the order details repeated on every row. Use this for revenue-by-product analysis, inventory reports, and return rate calculations.

Twitter / X API v2

Twitter's v2 API uses a "side-loaded" pattern — related objects are in a separate includes key rather than embedded in each tweet. This requires a manual join before you can produce a flat CSV.

What a Twitter search response looks like

{
  "data": [
    {
      "id": "1346889436626259968",
      "text": "Just shipped a new feature!",
      "author_id": "2244994945",
      "created_at": "2024-01-06T18:40:40.000Z",
      "public_metrics": {
        "retweet_count": 12,
        "reply_count": 3,
        "like_count": 47,
        "quote_count": 2
      }
    }
  ],
  "includes": {
    "users": [
      {
        "id": "2244994945",
        "name": "Twitter Dev",
        "username": "TwitterDev",
        "public_metrics": {
          "followers_count": 571234,
          "following_count": 2048
        }
      }
    ]
  },
  "meta": {
    "newest_id": "1346889436626259968",
    "result_count": 10,
    "next_token": "abc123"
  }
}

Joining tweets to their authors

import pandas as pd
import json

with open("twitter_response.json") as f:
    response = json.load(f)

# Flatten tweets
tweets_df = pd.json_normalize(response["data"], sep=".")

# Flatten included users
users_df = pd.json_normalize(response["includes"]["users"], sep=".")
users_df = users_df.rename(columns={
    "id": "author_id",
    "name": "author_name",
    "username": "author_username",
    "public_metrics.followers_count": "author_followers",
})
users_df = users_df[["author_id", "author_name", "author_username", "author_followers"]]

# Join on author_id
df = tweets_df.merge(users_df, on="author_id", how="left")

# Clean up column names
df = df.rename(columns={
    "public_metrics.retweet_count": "retweets",
    "public_metrics.like_count": "likes",
    "public_metrics.reply_count": "replies",
})

keep = ["id", "created_at", "text", "author_username", "author_name",
        "author_followers", "retweets", "likes", "replies"]
df = df[[c for c in keep if c in df.columns]]

df.to_csv("tweets.csv", index=False)
print(f"Exported {len(df)} tweets")

The fast path: online converter

For one-off exports from any API, the JSON to CSV converter handles the most common cases without writing a line of code:

  1. Make your API call in a browser, Postman, Insomnia, or curl
  2. Copy the response JSON
  3. Paste it into the converter
  4. Download CSV or Excel

The converter automatically:

  • Unwraps common envelope patterns (data, items, results)
  • Flattens nested objects to dot-notation column names
  • Joins primitive arrays (tags, labels) into pipe-delimited strings
  • Handles missing fields across records

When the online converter is enough:

  • You need a one-time export from Stripe, HubSpot, GitHub, or any other API
  • The response is a flat array or one level of nesting
  • You don't need to paginate (the first page has all the records you need)

When you need Python:

  • You need to paginate through thousands of records
  • You need to handle the side-loaded pattern (Twitter, JSON:API)
  • You want to schedule the export to run automatically
  • The response has 3+ levels of nesting that require custom logic

For everything in between, try the converter first. If the output looks wrong — missing columns, [object Object] values, or the wrong row structure — switch to the pandas approach for that specific API.

The pagination problem

Most APIs return 20–100 records per request. Getting everything requires following pagination. Each API does it differently:

| API | Pagination style | How to detect end | |-----|-----------------|-------------------| | Stripe | Cursor (starting_after) | has_more: false | | GitHub | Page number | Empty array response | | Shopify | Page info cursor | No pageInfo.hasNextPage | | Twitter v2 | next_token | No next_token in meta | | HubSpot | Cursor (after) | No paging.next in response |

A generic pagination loop:

import requests

def fetch_all_pages(url, params, auth_header, data_key="data", next_key="next"):
    """
    Generic paginator for cursor-based APIs.
    Adjust data_key and next_key for each API.
    """
    records = []
    while url:
        resp = requests.get(url, params=params, headers=auth_header)
        resp.raise_for_status()
        body = resp.json()

        # Handle both array root and envelope pattern
        if isinstance(body, list):
            records.extend(body)
            break  # Arrays don't paginate this way
        else:
            records.extend(body.get(data_key, []))
            # Get next page URL or cursor
            next_url = body.get(next_key) or body.get("meta", {}).get("next")
            url = next_url
            params = {}  # Clear params — next URL already has them

    return records

Handling rate limits

APIs throttle requests. Hitting a rate limit mid-export means incomplete data. Basic handling:

import time
import requests

def get_with_retry(url, headers, params, max_retries=3):
    for attempt in range(max_retries):
        resp = requests.get(url, headers=headers, params=params)

        if resp.status_code == 429:
            # Rate limited — check for Retry-After header
            retry_after = int(resp.headers.get("Retry-After", 60))
            print(f"Rate limited. Waiting {retry_after}s...")
            time.sleep(retry_after)
            continue

        resp.raise_for_status()
        return resp

    raise Exception(f"Failed after {max_retries} retries")

Frequently Asked Questions

What's the easiest way to convert an API response to CSV without coding?

Copy the JSON response from your browser, Postman, or curl and paste it into the JSON to CSV converter. It handles nested objects, missing fields, and primitive arrays automatically. Download CSV or Excel in one click.

My API response has a data wrapper — how do I get just the records?

Most converters unwrap common envelope patterns automatically. In Python: records = response["data"] before passing to pd.json_normalize. In the online converter, paste the full response — it detects and unwraps data, items, and results keys automatically.

How do I export more than one page of results?

You need to paginate using the API's cursor or page token. Each API is different — see the pagination table above for the pattern each major API uses. The Python examples in the Stripe and GitHub sections show working pagination loops.

My CSV has columns like payment_method_details.card.brand — can I rename them?

Yes. In pandas: df.rename(columns={"payment_method_details.card.brand": "card_brand"}). In Excel, rename the column headers after import. The online converter produces dot-notation column names by default — rename in your spreadsheet.

Can I convert the API response to Excel (.xlsx) instead of CSV?

Yes. Use the JSON to Excel converter for one-click .xlsx output. In Python, replace df.to_csv(...) with df.to_excel("output.xlsx", index=False, engine="openpyxl"). Excel files preserve date formatting and don't have the encoding ambiguity of CSV.

Why do some fields come back empty in my CSV?

APIs return optional fields only when they have a value. A payment without a refund won't have a refunded_at field at all — it simply won't appear in that record. pandas fills these with NaN (empty in CSV). This is correct behavior. Use df.fillna("") if you want explicit empty strings instead of NaN.

All articles