Generating an hreflang XML Sitemap From a Spreadsheet With Python

Multilingual sites often keep their page inventory in a spreadsheet long before anyone builds a proper CMS integration. That spreadsheet is also the easiest place to generate hreflang annotations from. Putting hreflang in the XML sitemap, rather than in every page head, keeps templates clean and makes the mapping reviewable in one file.

This post shows a small script that turns a CSV of page groups into a valid sitemap with alternate links.

The input format

One row per page, with a group id that ties translations together:

group,lang,url
home,en,https://example.com/en/
home,zh-Hans,https://example.com/zh/
home,ja,https://example.com/ja/
pricing,en,https://example.com/en/pricing/
pricing,zh-Hans,https://example.com/zh/pricing/

The group id is the important column. Every URL in a group lists every other URL in the same group, including itself.

The script

import csv
from collections import defaultdict
from xml.sax.saxutils import escape

groups = defaultdict(list)
with open("pages.csv", newline="", encoding="utf-8") as f:
    for row in csv.DictReader(f):
        groups[row["group"]].append((row["lang"], row["url"]))

out = ['<?xml version="1.0" encoding="UTF-8"?>',
       '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9" '
       'xmlns:xhtml="http://www.w3.org/1999/xhtml">']
for gid, pages in groups.items():
    default = next((u for l, u in pages if l == "en"), pages[0][1])
    for lang, url in pages:
        out.append("  <url>")
        out.append(f"    <loc>{escape(url)}</loc>")
        for l2, u2 in pages:
            out.append(f'    <xhtml:link rel="alternate" hreflang="{l2}" href="{escape(u2)}"/>')
        out.append(f'    <xhtml:link rel="alternate" hreflang="x-default" href="{escape(default)}"/>')
        out.append("  </url>")
out.append("</urlset>")
open("sitemap-hreflang.xml", "w", encoding="utf-8").write("\n".join(out))
print(sum(len(p) for p in groups.values()), "urls written")

Checks worth adding

• Duplicate languages in a group. Two en rows in one group usually means a copy-paste error. Raise an exception.
• Language codes. Validate against a small allowlist such as en, zh-Hans, zh-Hant, ja, ko, es. Typos like zh_CN with an underscore are invalid.
• Orphans. A group with only one URL produces no useful alternates. Log it so someone can decide whether a translation is missing.
• Status codes. Before publishing, request each URL and drop anything that does not return 200. Hreflang pointing at redirects or 404s is ignored.

Why x-default matters

x-default tells search engines which version to show users whose language you do not support. For most international sites that is the English page or a language selector. Leaving it out is not an error, but adding it removes ambiguity.

Deploying

Reference the new file from robots.txt with a Sitemap: line and submit it in Search Console and Bing Webmaster Tools. Keep the CSV in version control so translation changes are reviewed like code.

For the broader picture, including URL structure and common mistakes, see our hreflang guide for multilingual sites.

Published by PandaWM, an overseas marketing team based in Hong Kong.