The Spreadsheet That Ran Out of Rows — Python Bug Hunt

Briefing

Modelled on Public Health England's October 2020 reporting failure: nearly 16,000 positive COVID-19 test results were left out of the daily figures, and their contacts were not traced, for about a week. Lab results arrived as CSV files and were loaded into Excel templates in the legacy XLS format, which holds at most 65,536 rows per sheet; everything past the limit was silently dropped.

exporter.py turns records into legacy sheets. It stops at the row limit without a word.

Fix export_sheets so every record is exported, split across as many sheets as it takes.

Bug report

BUG-XLSROWS · Priority: Critical (data loss) · Reported by: data pipeline

export_sheets(records, max_rows=None) — max_rows defaults to limits.XLS_MAX_ROWS (65,536):

• returns a list of sheets; every sheet is [limits.HEADER] followed by up to max_rows - 1 records, so no sheet has more than max_rows rows
• every record appears exactly once, in the original order; sheets are filled completely before the next one starts
• no records -> [[HEADER]]
• max_rows < 2 cannot hold a record: raise ValueError

Observed: a 70,000-row lab file produced one full sheet; 4,465 results were never uploaded, with no error.

Logs

[ingest] lab_feed_2020-10-01.csv: 70000 rows
[export] wrote results.xls (65536 rows)
[upload] 65535 results uploaded

The code as shipped

src/pipeline/exporter.py (editable)

limits = bug_require("./limits.py")


def export_sheets(records, max_rows=None):
    if max_rows is None:
        max_rows = limits.XLS_MAX_ROWS
    sheet = [limits.HEADER]
    for record in records:
        if len(sheet) >= max_rows:
            break
        sheet.append(record)
    return [sheet]

Read-only context: src/pipeline/limits.py.