Spreadsheet import: column guessing takes a partial match before an exact one, so \"Applied through\" becomes the date #465

New issue

Closed

opened 2026-10-01 10:46:54 +00:00 by tiagoagueda · 0 comments

tiagoagueda commented

2026-10-01 10:46:54 +00:00

Owner

Found on 2026-09-29 in a read-only review of postulo at 95348908d, reading guess_mapping and running it against a few header rows.

Checked again on 2026-10-01 against main at 0a5cad587: still present. The code is now at core/csv_import.py:501-519 (guess_mapping).

guess_mapping gives each header the first field in SYNONYMS whose words match it exactly or as a leading or trailing word, all in one pass. applied_at comes early in SYNONYMS and holds the short words "applied" and "date", so a header that is an exact synonym of a later field, or merely ends in "date", is taken as the applied date first. The real date column that follows then finds applied_at already taken and is guessed as "ignore".

Where: core/csv_import.py:497-515, guess_mapping: the exact test (name in words) and the prefix/suffix test (name.startswith(word + " ") or name.endswith(" " + word)) sit in the same condition inside the loop over SYNONYMS, which is ordered ... status, applied_at, deadline, ... channel ....
What happens: run against the snapshot:
- Company, Role, Applied through, Date applied is guessed as company, role, applied_at, ignore, although "applied through" is listed word for word among the channel synonyms;
- Company, Role, Deadline date, Date applied and Company, Role, Interview date, Date applied give the same result.
  With Company,Role,Status,Applied through,Date applied and the row Acme,Dev,Interviewing,LinkedIn,2026-09-01, parse_rows returns applied_at=None, an empty channel and the note "Applied through: LinkedIn", and the row becomes an application with no date. The same columns in the other order (Date applied before Applied through) are guessed correctly.
Why it matters: the applied date is what the timeline, the insights and the report count from, and the channel is lost to a note. Every guess can be corrected on the mapping page, but the preview only shows five columns of a wide sheet, and nothing flags the wrong guess.
Fix: guess in two passes. First assign every header whose normalised name is an exact synonym. Then, for the headers still unmatched and the fields still free, accept a prefix or suffix match, preferring the longest matching synonym across all fields (so "deadline date" goes to deadline through "deadline", not to applied_at through "date").

Done when

A test in tests/test_csv_import.py asserts that guess_mapping(["Company", "Role", "Applied through", "Date applied"]) is ["company", "role", "channel", "applied_at"], and that guess_mapping(["Company", "Role", "Deadline date", "Date applied"]) maps the third column to deadline and the fourth to applied_at.

Found on 2026-09-29 in a read-only review of postulo at 95348908d, reading `guess_mapping` and running it against a few header rows. Checked again on 2026-10-01 against `main` at `0a5cad587`: still present. The code is now at `core/csv_import.py:501-519` (`guess_mapping`). `guess_mapping` gives each header the first field in `SYNONYMS` whose words match it exactly **or** as a leading or trailing word, all in one pass. `applied_at` comes early in `SYNONYMS` and holds the short words "applied" and "date", so a header that is an exact synonym of a later field, or merely ends in "date", is taken as the applied date first. The real date column that follows then finds `applied_at` already taken and is guessed as "ignore". - **Where:** `core/csv_import.py:497-515`, `guess_mapping`: the exact test (`name in words`) and the prefix/suffix test (`name.startswith(word + " ") or name.endswith(" " + word)`) sit in the same condition inside the loop over `SYNONYMS`, which is ordered `... status, applied_at, deadline, ... channel ...`. - **What happens:** run against the snapshot: - `Company, Role, Applied through, Date applied` is guessed as `company, role, applied_at, ignore`, although "applied through" is listed word for word among the `channel` synonyms; - `Company, Role, Deadline date, Date applied` and `Company, Role, Interview date, Date applied` give the same result. With `Company,Role,Status,Applied through,Date applied` and the row `Acme,Dev,Interviewing,LinkedIn,2026-09-01`, `parse_rows` returns `applied_at=None`, an empty channel and the note "Applied through: LinkedIn", and the row becomes an application with no date. The same columns in the other order (`Date applied` before `Applied through`) are guessed correctly. - **Why it matters:** the applied date is what the timeline, the insights and the report count from, and the channel is lost to a note. Every guess can be corrected on the mapping page, but the preview only shows five columns of a wide sheet, and nothing flags the wrong guess. - **Fix:** guess in two passes. First assign every header whose normalised name is an exact synonym. Then, for the headers still unmatched and the fields still free, accept a prefix or suffix match, preferring the longest matching synonym across all fields (so "deadline date" goes to `deadline` through "deadline", not to `applied_at` through "date"). ## Done when - A test in `tests/test_csv_import.py` asserts that `guess_mapping(["Company", "Role", "Applied through", "Date applied"])` is `["company", "role", "channel", "applied_at"]`, and that `guess_mapping(["Company", "Role", "Deadline date", "Date applied"])` maps the third column to `deadline` and the fourth to `applied_at`.

tiagoagueda added this to the 0.6.0 milestone

2026-10-01 10:46:54 +00:00

tiagoagueda added the

tier

bug

labels

2026-10-01 10:46:54 +00:00

tiagoagueda referenced this issue from a commit

2026-10-04 03:10:34 +00:00

Spreadsheet import guesses the channel and the real date column correctly

tiagoagueda closed this issue

2026-10-04 03:10:34 +00:00