Skip to content

CSV templates: inline instructions as # comment lines

Problem

Issue #2026 asked for filling instructions inside the CSV templates. The delivered pack puts them in the data area, one instruction per row, ending with "delete all these instructions lines before uploading". That makes the instructions data:

  • A user who forgets to delete them gets 4 to 11 failed rows in the upload report, drowning the real errors. equipment_scientific (160 rows) and researchfacilities_common (85 rows) double as reference lists, so the instructions sit inside the list people scroll.
  • The template stops round-tripping. Download, fill, upload is the whole point of the file.

Three further defects in the same pack fail on upload regardless:

  1. travel_trains_template.csv and headcount_template.csv are latin-1 (è of Genève, non-breaking spaces in "Other teaching staff"). Ingestion decodes utf-8-sig strictly, so both die with "Wrong CSV format or encoding" before a row is read. No file in the pack carries the UTF-8 BOM that #2069 added so Excel stops mangling accents on re-save.
  2. Every example date is US M/D/YYYY while the instruction line in the same file says ISO. DepartureDateMixin.parse_departure_date swaps / for - then calls fromisoformat, so 12-1-2025 raises: every example row in both travel templates is rejected.
  3. Four filenames differ from what templateMapping.ts requests, so the download button 404s.

Approach

Comment lines, not a second instructions file. Every ingestion path already funnels through csv_dict_reader, so one change covers uploads, factors, reference data and year config.

A line is a comment when # opens it at a record boundary. A # opening a physical line inside a quoted multi-line field is data, and quote parity tells the two apart — the existing test_csv_dict_reader_preserves_embedded_newlines_in_quoted_fields shows those fields are real here. The marker must be the first character of the line: equipment_data.csv already holds a cell reading #03 INN 215, and a rule loose enough to catch that would silently eat a data row.

Comments are stripped before delimiter sniffing, so comma-heavy instruction prose in a semicolon file cannot sway the detected delimiter.

Steps

  • strip_comment_lines in app/utils/csv_dialect.py, applied inside csv_dict_reader before sniffing. Unit tests for the quoted-field and #-inside-a-cell cases.
  • Convert the pack into frontend/public/templates/: instructions to # lines, dates to ISO, re-encode UTF-8 + BOM, the four renames.
  • Regression test walking every shipped template through the real reader (tests/unit/test_shipped_csv_templates.py). It fails on all three defects above, which is how they were found.
  • Replace with the re-delivered pack (SharePoint _DATA_INPUT/templates_2026-08-27). It fixes the encoding (UTF-8 + BOM everywhere), the dates, the # prefixes and one of the four filenames at the source.
  • Verify against the final pack (SharePoint _DATA_INPUT/templates_2026-09-01). Content-identical to our copy: the source now carries the unquoted # 3) line, the trimmed researchfacilities columns and every filename, including purchases_centralized_template.csv (the data owner is right that the submodule is centralized purchases, so templateMapping.ts now points there instead of purchases_additional_template.csv).
  • Test round with the data owners, then close #2026.

Divergences from the delivered pack (2026-09-01 delivery)

Everything we reported on the 2026-08-27 pack (quote-wrapped instruction line in processemissions, leftover empty column in both researchfacilities files, filenames) is fixed at the source. Our copy still differs on these points, reported so the next pack folds them in:

  • The closing "delete all these instructions lines" instruction now reads that # lines are ignored and can stay.
  • Instruction numbering renumbered (the pack skips 7 in travel_planes).
  • Bot review of the PR caught defects our first pass missed (see docs/code-review/2026-copilot-feedback-*.md for the triage):
  • Mojibake (UTF-8 re-encoded through cp1252: Genève, °C, …) in travel_trains, five purchases_* files and equipment_scientific, where the broken strings are sub_class factor keys the clean factor table would never match. Repaired.
  • Two instruction rows without their #: building_rooms line 2 (Instructons:, also a typo) and the quote-wrapped frequency list in external_ai. Both imported as failing data rows. Rewritten as # lines.
  • headcount instruction 3 named a column that does not exist (user_institution_id); travel_planes used NYC, a metro code the exact iata_code lookup cannot resolve (now JFK).
  • The regression suite now hunts instruction prose and mojibake in data rows, so marker-less instruction lines can't slip through again, and ingestion progress totals count the comment-stripped text.

Equipment factors (resolved)

The templates re-categorize the equipment catalog: scientific goes 138 → 161 classes, it 25 → 22, other 7 → 23 (Autoclaves, Photocopy machines, Kitchen… moved to other; Lab Freezer moved to scientific). The factor lookup is scoped per submodule and equipment requires a factor per row, so this only works with matching factors. Confirmed on #2026: the equipment factors were re-uploaded with the same categories, and the truncated Cellule axiale (biaxiale, triaxiale, etc. class is cut the same way on the factor side, so the exact match holds.

Out of scope

  • purchases_common_template.csv is absent from the pack and kept from the previous set; the Purchase module falls back to it.
  • MODULE_DEFAULTS in templateMapping.ts points Equipment at equipments_template.csv, which has never existed. Pre-existing 404, separate issue.
  • backend/INPUT_DATA/*_template.csv are unreferenced copies (nothing in app/ or tests/ reads them). Left alone; they want deleting, not updating.