gelbeseiten-to-xlsx skill

A Claude skill that scrapes a GelbeSeiten (German Yellow Pages) category page and turns every business listing on it into a formatted Excel contact list.

Given a URL like https://www.gelbeseiten.de/branchen/maler/oberursel-taunus the skill collects every company — name, address, phone, email, website, opening hours, rating, reviews, distance — and writes them to a styled .xlsx file, optionally deduplicating rows that share the same address.

When to use

Trigger this skill when the user:

• pastes a gelbeseiten.de/branchen/... URL and wants the companies in a spreadsheet,
• asks to "collect all <trade> in <town>" as an xlsx / contact list,
• references a file like Oberursel-maler.xlsx or asks for a Branchenbuch export.

Do not use it for single-company lookups or for non-GelbeSeiten sources.

Why the scripts exist

A naive scrape doesn't work, for three reasons the skill works around:

1. Only the first 50 results are server-rendered. The rest live behind a "Mehr Anzeigen" button that's unreliable under browser automation. The skill instead POSTs to GelbeSeiten's own /ajaxsuche paging endpoint.
2. Emails and websites aren't on the listing page — they're on each company's /gsbiz/<realid> detail page, mostly inside a JSON-LD LocalBusiness block. The skill fetches every detail page in parallel from the same origin.
3. Large result sets can't be returned through javascript_tool without truncation, so the rows are passed out via the clipboard.

Files

File	Role
SKILL.md	The procedure Claude follows (inputs, steps, output template, gotchas).
extract_listings.js	Runs inside the listing page via Claude-in-Chrome. Pages through /ajaxsuche, fetches every detail page, and stashes the rows on window.__rows.
build_xlsx.py	Reads the extracted JSON and writes the styled workbook. Supports --dedupe to merge rows by address.

Requirements

• Claude in Chrome MCP (mcp__Claude_in_Chrome__*) — to drive a real browser session on gelbeseiten.de so same-origin fetch() calls work.
• Python 3 with openpyxl for build_xlsx.py.
• A writable output folder (the script saves wherever you point it).

Usage

The full procedure is in SKILL.md; the short version:

1. Confirm inputs with the user: URL, output filename, whether to dedupe by address, and whether to match an existing template.
2. navigate to the listing URL in Chrome.
3. Paste extract_listings.js into javascript_tool and run await extractGelbeSeiten() — it auto-detects WAS/WO from the page's hidden #mod-LoadMore form, pages through all results, and fetches every detail page concurrently.
4. Copy the rows out via clipboard (focus the page first):

   await navigator.clipboard.writeText(JSON.stringify(window.__rows))
   

   then read the clipboard and save it as <trade>.json.
5. Build the workbook:

   python3 build_xlsx.py <input.json> <output.xlsx> "<Title>" \
       [--industry "Malerbetriebe"] [--province Hessen] [--dedupe]
   

   <Title> is the emoji + heading, e.g. 🎨 Maler – Oberursel (Taunus). The script appends · N Companies · Source: GelbeSeiten automatically.
6. Reload the xlsx, verify the row count and headers, spot-check a few rows, then deliver it with present_files.

Output format

The workbook has 22 columns and matches the existing Oberursel_taxi.xlsx template:

#, Company Name, Industry, Business Description, Province, ZIP Code, City,
Street, Contact Person, Telephone 1, Telephone 2, Telephone 3, Email,
Email 2, Website, Social Media, Opening Hours, Rating, # Reviews, Distance,
Badge / Partner, GelbeSeiten URL

Styling:

• Row 1: merged title bar, Arial 11 bold white on navy #0D2E4E, height 26.
• Row 2: column headers, Arial 9 bold white on blue #1F4E79, wrapped, height 30.
• Data: Arial 9, wrapped, alternating band fill #DEEAF1 / white.
• Freeze panes at C1 so # and Company Name stay visible while scrolling.

Facebook / Instagram links land in Social Media, not Website. Industry defaults to the trade (e.g. "Malerbetriebe"); Business Description comes from the JSON-LD keywords field; Province is the German state (Hessen for the Frankfurt/Taunus area by default).

Dedupe behaviour

With --dedupe, rows are grouped by (ZIP code, normalized street). Within each group:

• The most complete record wins (most filled fields, then longest name).
• Up to three distinct phone numbers are merged into Telephone 1/2/3.
• Empty fields (email, web, hours, rating) on the canonical row are filled from siblings when available.
• The longest Business Description is kept.

Street normalization treats Straße / Strasse / Str. / str as equivalent and strips non-alphanumerics, so "Hauptstraße 12" and "Hauptstr. 12" collapse correctly.