How to Export Every URL on a Website to CSV
By Seth Black · Published
To export every URL on a website to CSV, pull the URLs from the XML sitemap for a quick list, then crawl the site and export the crawl as CSV for the complete one. The sitemap gives you what the owner listed; the crawl gives you what’s actually linked, with a status code for each URL. Put both in the same spreadsheet and the differences are your audit.
This is the export half of how to see all pages on a website. That post compares every source of URLs. This one is about getting each source into a CSV you can sort, filter and diff.
How do you get all page URLs from a website?
There are four sources worth exporting, and they answer different questions:
| Source | What it contains | Works on any site | Row limit |
|---|---|---|---|
| XML sitemap | URLs the owner wants indexed | Yes | 50,000 per sitemap file |
| Crawl export | URLs reachable by links, with status codes | Yes | None on your own machine |
| Search Console | URLs Google knows about, by index status | Only yours | 1,000 per report export |
| Wayback Machine | URLs ever archived, including deleted ones | Yes | Paged, very large |
Start with the sitemap because it takes ten seconds. Then crawl, because the sitemap is never the whole story.
Exporting the sitemap to CSV
Find the sitemap first. Check /robots.txt for a Sitemap: line, then try /sitemap.xml, /sitemap_index.xml and /wp-sitemap.xml. Our free XML sitemap checker does that discovery for you: it reads robots.txt and the common locations, validates what it finds against the sitemaps.org protocol, and spot-checks the status of a sample of listed URLs.
For a single sitemap file:
echo "url" > sitemap-urls.csv
curl -s https://example.com/sitemap.xml \
| grep -oE '<loc>[^<]+' \
| sed 's/<loc>//' >> sitemap-urls.csv
Most sites of any size use a sitemap index, which lists child sitemaps instead of pages. This Python script follows the index, handles gzipped children, and writes url,lastmod,sitemap rows:
import csv, gzip, sys, urllib.request
import xml.etree.ElementTree as ET
NS = {"sm": "http://www.sitemaps.org/schemas/sitemap/0.9"}
def fetch(url):
data = urllib.request.urlopen(url, timeout=30).read()
return gzip.decompress(data) if data[:2] == b"\x1f\x8b" else data
def walk(url, writer):
root = ET.fromstring(fetch(url))
for child in root.findall("sm:sitemap/sm:loc", NS):
walk(child.text.strip(), writer)
for u in root.findall("sm:url", NS):
lastmod = u.findtext("sm:lastmod", default="", namespaces=NS)
writer.writerow([u.findtext("sm:loc", namespaces=NS).strip(), lastmod, url])
writer = csv.writer(sys.stdout)
writer.writerow(["url", "lastmod", "sitemap"])
walk(sys.argv[1], writer)
Run it with python sitemap_to_csv.py https://example.com/sitemap_index.xml > sitemap-urls.csv. The sitemap column tells you which child file each URL came from, which matters when one plugin’s sitemap is stale and the others aren’t.
Exporting a crawl to CSV
A crawl finds URLs by following links from the home page, and it records what each URL actually returned. That’s the column the sitemap can’t give you. With bseoa:
black-seo-analyzer \
--url-to-begin-crawl https://example.com \
--disable-external-links \
--output-type csv \
--output-file crawl.csv
The CSV has one row per crawled URL with these columns: URL, Status Code, Redirect To, Crawl Timestamp, Title, Description, Word Count, H1 Count, Internal Links, External Links, Images, Web Vitals and Warnings. If you only want the URL list with status codes, --output-type csv-flat writes URL, Status Code, Redirect To, Crawl Timestamp and Warnings Count.
The crawl is stored in a local database (crawl.db by default), so you can write other formats later without crawling again:
black-seo-analyzer --generate-output --output-type csv-flat --output-file urls.csv
That uses the latest crawl session; pass --session-id for an older one. Add --spa if the site renders links with JavaScript, or the crawl will stop at the home page.
You can also crawl the sitemap’s URLs directly, which checks the status of every listed URL in one pass:
black-seo-analyzer \
--url-to-begin-crawl https://example.com/sitemap.xml \
--is-sitemap \
--output-type csv \
--output-file sitemap-crawl.csv
Search Console and the Wayback Machine
Search Console: Indexing, then Pages. Click a status (indexed, or a not-indexed reason), then Export. Each export is capped at 1,000 rows. On a large site, add a URL-prefix property for each big section so each gets its own 1,000-row sample.
Wayback Machine: the CDX API returns URLs as plain text, one per line, which is a one-column CSV:
curl -s 'https://web.archive.org/cdx/search/cdx?url=example.com/*&fl=original&collapse=urlkey' \
> archived-urls.csv
This is the source for old URLs that still need redirects after a migration.
How do you compare the exports?
Once you have two lists, compare them. On the command line, strip each file to one sorted column of URLs:
tail -n +2 sitemap-urls.csv | cut -d, -f1 | sort -u > a.txt
tail -n +2 urls.csv | cut -d, -f1 | tr -d '"' | sort -u > b.txt
comm -23 a.txt b.txt > in-sitemap-not-linked.txt
comm -13 a.txt b.txt > linked-not-in-sitemap.txt
In a spreadsheet, put both lists on separate sheets and use COUNTIF or XLOOKUP to mark each URL as present or missing in the other.
What the differences usually mean:
- In the sitemap, not reached by the crawl: orphan pages, or pages only linked from JavaScript the crawler didn’t run. See how to find orphan pages.
- Reached by the crawl, not in the sitemap: the sitemap generator skips a post type, or the site generates URLs (tags, filters, pagination) it never meant to publish.
- In the sitemap with a non-200 status: redirected or deleted pages your sitemap still advertises. Fix those first; they waste crawl requests on every visit.
Mistakes that spoil the export
Opening the CSV in Excel and saving it back. Excel can reformat long numbers and dates and may change the encoding. Keep the original file and work on a copy.
Mixing http, www and trailing-slash variants. Normalize before comparing, or every URL looks missing from the other list. The crawler flags internal URLs that end in a slash as trailing slash in URL, which helps you spot the pattern. Better yet, treat the variants as a finding: a site that links to both /page and /page/ has a canonical problem worth fixing; see how to find and fix every broken canonical tag.
Exporting once and calling it done. URL lists go stale with every deploy. Script the crawl and the diff, and rerun it after releases.
-Sethers