How to Scrape a Website to CSV or JSON

Every “export to CSV” button on the web wants you to paste a URL into someone else’s server and trust their row limits. I went the other way this morning: a 10-page crawl of quotes.toscrape.com through Magpie and jq finished in 33 seconds and landed on my disk as an 11-line CSV, no account and no API key involved. This guide covers when to scrape website to CSV instead of JSON, then the exact commands for every shape of job: one page, a whole site, and typed records when you need them.

Decide by who opens the file

The format question answers itself once you name the consumer.

The file’s consumer	Pick	Why
Excel, Sheets, a manager	CSV	One record per row, opens by double-click
jq, Python, a Go program	JSON	Types and nesting survive the round-trip
An LLM prompt or RAG store	JSON (or markdown)	Nested fields stay intact; no comma-quoting hazards
A BI tool or SQL import	CSV	Flat rows are the native model

Two things CSV cannot hold: nesting and types. A list of authors on one row becomes a quoted, semicolon-mangled string; every number arrives as text. JSON keeps both, and any spreadsheet that truly needs the data will take a CSV you generate from that JSON in one line of jq. So the working rule I use: scrape to JSON by default, convert to CSV at the last moment, and keep the JSON file as the archive.

Before you start

• Magpie — a local Go CLI. Clone and build it, no runtime dependencies:

  git clone https://github.com/motherlodelab/magpie.git
  cd magpie && go build -o magpie ./cmd/magpie

  License is AGPL-3.0-or-later or commercial. Everything in Steps 1–3 runs with no API key; the README is explicit that cleaning, mapping, crawling, and verticals need none.
• jq — any 1.6+ works; I used 1.7.1.
• No Python, no browser automation setup. Magpie pulls in a real browser only when a page forces it. If you already live in pandas and your target is one HTML table, the table-scraping guide covers that route with its pandas 3.0 gotchas; this page is for everything else.

Step 1: Scrape one page to JSON

One page, one command, and the output is already the format you’d want to keep:

magpie scrape 'https://quotes.toscrape.com/' --format json --out page.json

This ran in 3.3 seconds on my connection. The file is a single JSON object with five fields: url, final_url (where a redirect landed), title, markdown (the cleaned body), and structured_data (the page’s JSON-LD, when it has any). Pull what you need:

jq -r '.title' page.json
# Quotes to Scrape

If your “CSV” was always going to be one page’s fields, you may be done here: the JSON is the record. What you should never see is HTML soup. If a scrape comes back oddly empty, that is usually the quality gate: Magpie refuses pages whose cleaned text is under roughly 200 words and prints quality blocked (empty) rather than handing you a footer-and-cookie-banner file. Annoying once, correct forever.

For a deeper treatment of the one-page scrape, see how to scrape data from a website; this page stays on the file-format question.

Step 2: Crawl a whole site into rows, no LLM

This is the step that produces most people’s CSV. Magpie’s crawl command has a --corpus mode that needs no schema and makes no LLM calls: it visits pages and emits one JSONL record per page with url, title, depth, and cleaned markdown.

magpie crawl 'https://quotes.toscrape.com/' --corpus \
  --max-pages 10 --concurrency 4 --out pages.jsonl
# crawl: run_id=ee1abc845bdc8c55-311992 pages_ok=10 pages_err=0 records=10

Ten pages took 32.7 seconds at concurrency 4 from a home connection. The JSONL is the archive; the CSV is a view you regenerate whenever you feel like it:

{ echo 'url,title'; jq -r '[.url, .title] | @csv' pages.jsonl; } > pages.csv

Real output from this morning’s run:

url,title
"https://quotes.toscrape.com/","Quotes to Scrape"
"https://quotes.toscrape.com/author/Albert-Einstein","Quotes to Scrape"
"https://quotes.toscrape.com/author/Eleanor-Roosevelt","Quotes to Scrape"

Three failure modes worth knowing before you run it on a site you care about:

• Sitemap 404. quotes.toscrape.com has no sitemap.xml; Magpie printed one line (GET https://quotes.toscrape.com/sitemap.xml: HTTP 404; continuing seed-only) and followed links instead. Harmless.
• Robots.txt. Disallowed paths are skipped by default; --ignore-robots exists and prints a warning when used. Leave it alone.
• Unbounded crawls. --max-pages and --same-host (on by default) are your seatbelts. A crawl is checkpointed in SQLite, so an interrupted run resumes with --resume instead of starting over.

If you want the columns to carry real fields per page rather than page-level metadata, that is what the next two steps add.

Step 3: Typed rows with no selectors and no LLM

Magpie ships 27 built-in verticals (magpie vertical --list): extractors for articles, arXiv papers, events, job postings, local businesses, GitHub repos, and more. Each maps one known page type to a typed record; some read the page’s JSON-LD, some query the site’s own export API, and none cost an API call:

magpie scrape 'https://arxiv.org/abs/1706.03762' --vertical arxiv --format json --out paper.json

Abridged real output:

{
  "vertical": "arxiv",
  "record": {
    "title": "Attention Is All You Need",
    "authors": ["Ashish Vaswani", "Noam Shazeer", "..."],
    "published": "2017-06-12T17:57:34Z",
    "summary": "The dominant sequence transduction models are based on..."
  }
}

Flatten to CSV when the spreadsheet moment comes:

jq -r '.record | [.title, (.authors | join("; ")), .published] | @csv' paper.json
# "Attention Is All You Need","Ashish Vaswani; Noam Shazeer; ...","2017-06-12T17:57:34Z"

If no vertical handles the URL, Magpie says so plainly (vertical "arxiv": vertical extractor does not handle URL) and you fall through to Step 4. Do not improvise a vertical by regexing the markdown; the refusal is the tool telling you the page type is unknown, and a wrong guess is worse than an error.

Step 4: Your own fields: schema plus an LLM

When the fields you want exist only in prose, Magpie takes a JSON Schema and fills it with any LLM provider (Anthropic, OpenAI, Ollama for local models, and others), on both scrape and crawl:

# quote.yaml
$schema: "https://json-schema.org/draft/2020-12/schema"
type: object
required: [quote, author]
properties:
  quote: { type: string }
  author: { type: string }
  tags: { type: array, items: { type: string } }

magpie crawl 'https://quotes.toscrape.com/' --schema quote.yaml \
  --provider ollama --format csv --out quotes.csv

Note the flag: with a schema, crawl writes CSV natively (--format csv|jsonl|json|sqlite). This is the one route that skips jq entirely. I could not live-run it for this article because there is no LLM key on this machine, so treat the command shape as verified against --help and the schema format against Magpie’s own testdata, not against a measured run. Set --max-cost before you let it loose on a few hundred pages; the cap aborts before the bill does.

Where “scrape website to CSV” breaks

• Very thin pages. My first JS-rendering demo for this article was quotes.toscrape.com/js/, which builds its ten short quotes with JavaScript. Browser render, explicit wait, still refused: the cleaned text sits under the ~200-word quality floor. That gate exists so you don’t archive cookie banners; it will also refuse genuinely tiny pages. Verticals sidestep it because they read structured sources, JSON-LD and site APIs, rather than cleaned prose.
• Heavily JS-dependent sites. --render browser plus --action lines (click, type, wait) handle pages that need interaction. That is an article of its own; see scraping dynamic websites for the full walkthrough.
• Type loss on the way to CSV. Vertical amount fields return bare numbers, currency dropped: an Amazon price arrived as {"amount":1540.45} from a page priced in baht. Keep the JSON; never let the CSV be your only copy.
• Blocked sites. Magpie respects robots.txt and self-throttles, and it does not defeat CAPTCHAs or bot walls, by design. The legality picture, such as it is, lives in our piece on whether web scraping is legal.

Make it stick

Put the crawl on a schedule and treat pages.jsonl as the dataset: cron the crawl weekly, regenerate any CSV from it with jq in a second, and never re-fetch for a formatting change. Interrupted runs resume from the SQLite checkpoint. For pipelines that want records as they happen, crawl’s --exporter-cmd pipes each JSONL record to another program’s stdin while the run is still going.

If the real destination is a spreadsheet, two pages cover that end: importing website data into Excel and web scraping with Google Sheets. Tables specifically get their own guide too.

FAQ

Does Magpie export CSV directly? Crawl does, with --schema and --format csv. Scrape does not: its --format flag accepts json and jsonl only. The jq one-liner in Step 2 covers the gap in under a second.

Can Excel open the JSON file? Not usefully; Excel wants flat rows and will mangle nested objects. Generate a CSV from the JSON with jq, or use crawl --schema --format csv and skip the conversion.

Do I need an API key? Not for Steps 1 through 3: scraping, corpus crawling, and verticals make zero LLM calls. Only schema extraction (Step 4) needs a provider, and Ollama keeps that local and free.

How many pages can one crawl handle? --max-pages and --max-depth bound it, the frontier is rate-limited per host, and checkpoints make long runs resumable. Start at 10 pages and scale after you have inspected the output.

Is this allowed on any site? robots.txt is respected by default and polite throttling is built in, but a site’s terms can still forbid scraping. Read them, and check the legal overview before scraping something you do not own.

Start with Step 2 on the smallest site you actually want data from. Ten pages is enough to learn whether your columns are right, and the JSONL you keep is worth more than any single export.