The goal
Aggregate UK job listings from HireMyVisa, verify which employers are DEFINITELY or LIKELY sponsors, enrich the data, and publish it as a clean Excel workbook the sales team can filter and send.
The solution
A Python pipeline that authenticates against HireMyVisa, paginates the jobs API with stop-when-empty logic, dedupes by job_id, enriches employer sponsorship via a batch-of-25 POST to /api/sponsor/batch-check, persists intermediate JSON, and produces a pandas/openpyxl Excel workbook split into ≤5,000-row parts with formatted date columns, frozen headers, and Excel-safe character sanitization.
Highlights
- Stop-when-zero-new pagination adapts to any query size
- Minimal sponsor-API calls via batched dedupe
- Excel output split into user-friendly 5K-row workbooks
- Re-runnable thanks to intermediate JSON caches
The challenge
The jobs API doesn't return a total count or last-page marker — blind pagination wastes time.
Dict keyed by job_id tracks collected, count `new` per page, break the loop on new == 0 so the collector adapts to any query size.
Sponsor status lives in a separate API that accepts batches of 25 and needs Bearer auth.
Dedupe employer names across all jobs, chunk to 25, call /api/sponsor/batch-check per chunk, and map results back — minimizes sponsor calls even for 1000+ jobs.
Excel blocks control characters and large single workbooks open slowly.
Regex [\x00-\x08\x0B\x0C\x0E-\x1F] strips illegal chars at row construction; MAX_ROWS_PER_FILE = 5000 splits output across jobs_part_{index}.xlsx files.
Posted dates arrive as ISO strings with a Z suffix, but users want to sort/filter by real dates.
parse_posted_date + format_posted_date convert ISO to Python datetime so pandas → openpyxl writes a real Excel date cell.
What was delivered
- collector.py, api.py, enrich_sponsors.py, excel_builder.py, auth.py
- requirements.txt + bundled venv
- Intermediate JSON caches (data/jobs.json, data/jobs_enriched.json)
- Multi-part Excel output (data/jobs_part_<n>.xlsx)
- .env-driven credentials
Results
GB (UK)
Country
25/call
Sponsor batch
≤5K rows
File split
2 (jobs + sponsor)
APIs used
What it taught me
- 'Stop when new == 0' is the simplest reliable pagination for APIs without totals
- Excel-safe character stripping must happen at row construction, not after
- Intermediate JSON caches between stages make the pipeline debuggable and re-runnable