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) andresearchfacilities_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:
travel_trains_template.csvandheadcount_template.csvare latin-1 (èof Genève, non-breaking spaces in "Other teaching staff"). Ingestion decodesutf-8-sigstrictly, 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.- Every example date is US
M/D/YYYYwhile the instruction line in the same file says ISO.DepartureDateMixin.parse_departure_dateswaps/for-then callsfromisoformat, so12-1-2025raises: every example row in both travel templates is rejected. - Four filenames differ from what
templateMapping.tsrequests, 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_linesinapp/utils/csv_dialect.py, applied insidecsv_dict_readerbefore 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 trimmedresearchfacilitiescolumns and every filename, includingpurchases_centralized_template.csv(the data owner is right that the submodule is centralized purchases, sotemplateMapping.tsnow points there instead ofpurchases_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-*.mdfor the triage): - Mojibake (UTF-8 re-encoded through cp1252:
Genève,°C,…) intravel_trains, fivepurchases_*files andequipment_scientific, where the broken strings aresub_classfactor keys the clean factor table would never match. Repaired. - Two instruction rows without their
#:building_roomsline 2 (Instructons:, also a typo) and the quote-wrapped frequency list inexternal_ai. Both imported as failing data rows. Rewritten as#lines. headcountinstruction 3 named a column that does not exist (user_institution_id);travel_planesusedNYC, a metro code the exactiata_codelookup cannot resolve (nowJFK).- 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.csvis absent from the pack and kept from the previous set; the Purchase module falls back to it.MODULE_DEFAULTSintemplateMapping.tspoints Equipment atequipments_template.csv, which has never existed. Pre-existing 404, separate issue.backend/INPUT_DATA/*_template.csvare unreferenced copies (nothing inapp/ortests/reads them). Left alone; they want deleting, not updating.