← All posts

How to Export Scraped Data to CSV, JSON, or a Database

GUIDES · JUL 18, 2026 · 5 MIN READ

Quick answer: Match the destination to the job. Use CSV for a one-off handoff to a spreadsheet (and watch encoding and embedded commas). Use JSON or JSONL for nested data and large streaming sets. Use a database (SQLite or Postgres) the moment you scrape on a schedule, because a database gives you idempotent upserts, so a re-run updates rows instead of piling up duplicates. The one rule that saves you: give every record a stable unique key.

You scraped the data. Now where does it go? The answer decides whether your second run is clean or a mess of duplicates, and whether opening the file in Excel corrupts every accented character. Here is how to pick, with working code for each.

Pick the destination by how the data is used

Destination Best for Nested data Idempotent re-runs Big sets
CSV One-off, spreadsheet handoff No No Awkward
JSON Small nested exports, APIs Yes No Load-all-in-memory
JSONL Large streaming datasets Yes No Yes, line by line
Database Scheduled / repeated scrapes Yes (JSON cols) Yes Yes

The pattern: flat and disposable goes to CSV, nested goes to JSON, big and streamed goes to JSONL, and anything you run more than once goes to a database.

CSV: fine, but mind the traps

CSV is the universal handoff format, and it has three traps that bite everyone at least once.

Encoding. Write UTF-8 with a BOM if the file is destined for Excel on Windows, or accented characters and emoji turn into mojibake. Python's utf-8-sig handles this.

Embedded commas and quotes. A scraped field like "Acme, Inc." will shift every column to the right unless it is quoted. Never build CSV by joining on commas yourself; use a real writer that quotes correctly.

Newlines in fields. Scraped descriptions contain line breaks. A proper CSV writer escapes them; hand-rolled string concatenation does not.

import csv

rows = [
    {"name": "Acme, Inc.", "price": 549000, "note": "Great\ndeal"},
    {"name": "Café Ünïcode", "price": 12000, "note": "clean"},
]

with open("out.csv", "w", newline="", encoding="utf-8-sig") as f:
    writer = csv.DictWriter(f, fieldnames=["name", "price", "note"])
    writer.writeheader()
    writer.writerows(rows)

newline="" plus the csv module handles quoting and embedded newlines correctly, and utf-8-sig keeps Excel happy. That is the whole safe recipe.

JSON and JSONL: nested data and large sets

Plain JSON is right when your records nest (a product with a list of variants, a listing with a photo array) and the whole set fits in memory. You write one array.

import json

with open("out.json", "w", encoding="utf-8") as f:
    json.dump(rows, f, ensure_ascii=False, indent=2)

Set ensure_ascii=False so non-ASCII characters stay readable instead of becoming \uXXXX escapes.

The problem with plain JSON is scale: to read it you must load the entire array into memory. For large scrapes, use JSONL (one JSON object per line). You append as you go and read one line at a time, so memory stays flat whether the file has a thousand rows or ten million.

import json

# Writing: append one object per line as records arrive.
with open("out.jsonl", "a", encoding="utf-8") as f:
    for record in scraped_records:
        f.write(json.dumps(record, ensure_ascii=False) + "\n")

# Reading: stream, never load the whole file.
with open("out.jsonl", encoding="utf-8") as f:
    for line in f:
        record = json.loads(line)
        process(record)

JSONL is the format I reach for by default on anything I expect to grow, because it streams on both ends and it survives an interrupted run: the lines already written are still valid.

A database: the moment you scrape more than once

The instant your scrape runs on a schedule, files stop being enough. Run a CSV export twice and you have two files or double the rows. A database fixes this with an upsert: a unique key plus "insert, or update if it already exists," so a re-run refreshes rows in place. That is idempotency, and it is the single biggest reason to use a database.

SQLite needs no server and is perfect up to surprisingly large sizes. Postgres is the move when multiple writers or heavy queries arrive. The upsert syntax differs slightly; the idea is identical.

import sqlite3

con = sqlite3.connect("scraped.db")
con.execute("""
    CREATE TABLE IF NOT EXISTS products (
        id        TEXT PRIMARY KEY,   -- your stable unique key
        name      TEXT NOT NULL,
        price     INTEGER,
        raw       TEXT,               -- JSON blob for nested extras
        updated_at TEXT
    )
""")

con.execute("""
    INSERT INTO products (id, name, price, raw, updated_at)
    VALUES (:id, :name, :price, :raw, datetime('now'))
    ON CONFLICT(id) DO UPDATE SET
        name       = excluded.name,
        price      = excluded.price,
        raw        = excluded.raw,
        updated_at = datetime('now')
""", {"id": "sku-123", "name": "Widget", "price": 1999, "raw": "{}"})
con.commit()

Postgres is the same shape with ON CONFLICT (id) DO UPDATE SET .... The updated_at column gives you a free freshness signal: query for rows you have not seen recently to find stale or removed items.

The one thing that makes it all work: a stable key

Every idempotent pattern above depends on a unique key that is the same across runs. Derive it from the source, not from a row number. For a product, the SKU or the product URL. For a listing, a fingerprint of address plus coordinates. For an article, a normalized URL. Get the key right and re-runs are clean forever; get it wrong and you get duplicates no matter which storage format you chose.

Where this fits in a scraping pipeline

Export is the last mile, and it is easiest when the data arriving is already clean. If your fetch step returns structured JSON against a schema, the record is export-ready and you skip a whole parsing stage. That is the approach in extracting structured JSON from any webpage, and it pairs naturally with using live web data in a RAG pipeline where the same records feed a retrieval index instead of, or alongside, a database.

Quick decision guide

  • One-off for a colleague who opens it in Excel: CSV with utf-8-sig.
  • Nested data, small set, feeding an API: JSON.
  • Large set, growing, or an interruptible run: JSONL.
  • Runs on a schedule, needs to stay deduped: a database with upserts.

Pick with the second run in mind, not the first. The first run works with anything; the second is where the wrong choice turns into a cleanup job.

Scraping data you need to store cleanly? link.sc returns clean markdown or schema-bound JSON from any URL, so records land export-ready. Start free.