An import that tells you what it will do

A school that starts using software in the middle of a school year does not start with an empty database. It starts with spreadsheets: the students admitted last July, the second-year class running since October, the repeaters, the teaching staff as the registrar’s office keeps it. Fifteen hundred rows across four files, none written by a programmer.

Retyping them is not an option — a thousand students is a week of typing and a thousand chances to make a mistake, the kind you find in March.

So you write a bulk import, and the moment you do you inherit a problem that has almost nothing to do with parsing files:

An agent is about to press a button that will write fifteen hundred rows into a database they cannot inspect. They need to know what will happen before it happens — and they need that promise to be true.

This is the import subsystem of ENI-ABT, the admissions and records platform of an engineering school in Bamako. Around 1 900 lines across four files. Most of them exist because of that one sentence.

The bug that was there before any of this

The application already had an import screen. It had a model, a three-step wizard, a file upload, a row-by-row report, five declared import types, and a state machine ending in en_attente — pending.

Nothing ever came to pick it up. No Celery task, no management command, no button. The five declared import types had never created a single row in a business table.

The report was truthful — it simply said nothing about what would be imported, since nothing was.

Every visible part worked. The upload, the parser, the report, the badges in the right colour — a demo would have looked complete. The state machine’s last transition papered over the gap, because pending is indistinguishable from pending on something that exists.

Hence the test module’s first assertion, which is not about parsing at all:

What the application did not do at all.

Two passes, one code path

The design settled into two moments.

The trial. The whole file is run: objects are built, references resolved, errors collected — and then everything is rolled back. The report that comes out does not say here is what is possible. It says here is what will happen: thirty-four creations, six updates, two refusals and why.

The execution. An agent has read that report and asks for it. The same walk, without the rollback at the end.

The rule that makes this worth anything is one line:

The same path in both cases: a trial that followed a different path from the execution would prove nothing about what will happen.

The temptation is what most preview features do: a “validation” mode that checks types and required fields without touching the ORM, beside an “import” mode that does the real work. Two code paths, and a preview that is right about the easy failures and silent about the ones that matter — a PROTECT on a null foreign key, a unique constraint, a save() override that raises.

django-import-export gives you the honest version for free, and it is the only part of the library this project uses:

use_transactions is not decorative. The dry run really does write to the database before undoing everything; without a transaction, an import “just to see” would create the accounts and leave them there.

dry_run=True executes the inserts and updates, hits every constraint, runs every save(), and rolls back. That is what makes the report a promise instead of a guess — and why the transaction is load-bearing rather than tidy: turn it off and the preview becomes the import.

The matching test is the shortest in the file, and the one we would keep if we could keep only one:

The file, before any of it means anything

Before the two passes, the bytes. This part is unglamorous and it is where imports actually die.

The BOM is the best example of a bug invisible in every tool you would use to debug it. Excel writes EF BB BF at the head of a UTF-8 CSV; read as plain utf-8, the first header becomes matricule, which prints as matricule and compares equal to matricule in no language. The import reports required column missing: matricule while the agent stares at a column headed matricule, and there is no way for them to win that argument.

utf-8-sig strips it and falls back cleanly on files that have none. The legacy encodings follow because spreadsheets here produce as much CP1252 as UTF-8, and a file that fails to decode is a file someone will convert by hand, badly.

Then the separator, which is a regional question and not a technical one:

Counting occurrences in the header line is the right heuristic: it is the line most likely to contain the separator many times and least likely to contain quoted text. Getting it wrong does not raise — it produces one column holding the whole row, and an error about missing columns that makes no sense to the person reading it.

Headers then get one deliberate liberty — str(h).strip().lower(), so Matricule and MATRICULE are the same column. Rejecting a correct file over a capital letter costs a morning and buys nothing.

And .xlsx is a first-class input rather than a grudging one, because demanding a CSV export from a registrar’s office adds a manipulation where the accents get lost every other time. Every “please save as CSV” line in a manual is a place where TRAORÉ becomes TRAOR�.

Accepting what people write

Below the file sits the layer that decides what a cell means. Three rules govern it, and they pull against each other:

We accept what people write, not what the database expects. Nothing is guessed silently — an unknown department created on the fly would pollute the reference tables for years.

Those two together are the whole design. Be generous about form; be absolutely rigid about identity. 2ème and 2e année and L2 all mean the second year. ZZ does not mean a department, and no amount of goodwill will make it one.

The widgets hold the generosity. NiveauWidget carries a plain dictionary — "1re": 1, "2e": 2, "2ème": 2, "seconde": 2 — plus two fallbacks: a bare digit, and the L2 / M1 / A3 shape where the letter names the cycle and the digit the year. An explicit table beats a clever regex for a reason that shows up the first time you extend it: it is readable by the person who knows the domain and not the language. When the school starts writing T3, the fix is one line, and anyone can review it.

The generosity stops at a hard edge, and the edge is drawn in the error message:

The refusal carries the list of valid codes. That single detail is the difference between a report an agent can act on and one they have to escalate, and the test asserts it directly:

Note the line number. numero=rang + 2 — one for the zero index, one for the header row — because the agent is going to alt-tab to their spreadsheet and scroll to line 4. A report numbered from the parser’s point of view makes the reader do arithmetic to find their own data.

The ambiguity you cannot resolve, and what to do about it

2e année exists in five different degree cycles. The cell says 2. Which one?

There is no correct answer available from the data, and this is the interesting case: the code has to pick, and the picking has to be defensible and visible.

Three tiers, most specific first: an explicit cycle column wins; failing that, a year declared by the student’s own department; failing that, the engineering cycle, the school’s main programme.

The important word is affiché — displayed. A default that lives in a named module constant with a comment explaining the alternative is a decision. The same behaviour arising from annees.first() is an accident that will be rediscovered in a year by someone reading a bug report about a student enrolled in the wrong programme.

There is project history in that widget. Before it existed, tout import versait les gens en première année — every import dumped people into first year, so you could take on neither an existing second year nor the repeaters. Which are exactly the lists you have on hand when you start mid-year: the import had been designed for the one case that does not need it, since first-year students come from the admissions process the application already runs end to end.

Two passes again, for a different reason

The execution has a wrinkle that took a while to see. use_transactions means django-import-export rolls the whole batch back as soon as one row fails. That is the right default — but the agent has a checkbox that says import the valid rows anyway, and honouring it inside a single pass is impossible: by the time you know row 812 fails, rows 1 to 811 are inside a transaction that is about to be undone.

So the real import runs the trial first, and uses it as a filter:

Two full passes over the file. On five thousand rows — the hard cap, above which the job becomes a command-line data migration with someone watching — that is a few seconds, and it buys the property that matters: the set written is exactly the set the report described. It is also why there is no queue. The original design deferred to Celery; bounded, both passes finish inside a request, and a queue would add serialisation, a worker round-trip, a polling page and a class of failure — the task vanished — for nothing.

Note what the checkbox does not do. Les lignes en erreur ne passent pas — jamais : rows in error never pass. What ignorer_erreurs settles is the fate of the others — either the bad rows are set aside and the rest is kept, or nothing is kept until the file is clean. The second is the default, because a half-done import is harder to recover from than a refused one. The agent has to opt into partial success, and the failure message tells them the option exists:

Silencing the audit log on purpose

One line in that block deserves its own section:

The platform has an audit journal wired to model writes. It exists so that a registrar can answer who changed this student’s status, and when. A thousand- row import writes a thousand entries into it, and the journal becomes unusable on the day it is most needed.

sans_journal() suppresses per-row entries for the duration, and one entry is written for the operation itself:

That entry points at the Import row, which still holds the file and the row-by-row report. Nothing is lost — the granularity moved to where it is readable. An audit trail that records everything at the same level of detail is an audit trail nobody reads.

Depositing the same file twice

The normal gesture, after a report with three errors, is: fix those three lines, save, upload the same file again. Which means the import has to be safe to replay — and safe here has two distinct meanings.

No duplicates, which import_id_fields handles:

And no clobbering, which needs a decision:

Otherwise, re-uploading a file to fix three rows would rewrite the other thousand — and erase what an agent has corrected by hand since the previous import.

This is the rule easiest to get wrong, because “update if exists” reads like the helpful behaviour. It is not: the spreadsheet is a snapshot of a moment that has passed, the database is where work has happened since, and re-running an import must not roll that work back.

The mirror test ticks maj_existants and asserts the opposite.

The same file. Two behaviours. One checkbox, ticked by a human who knows which of the two they mean.

Replay also needs an exit: the rows that failed become rows in a table, keeping their original cell values, and the error export re-uploads as-is. Export, import and the downloadable blank template all read the same COLONNES, so the three cannot drift apart:

An export whose columns cannot be re-uploaded is just a printout. That is what makes bulk correction possible: pull the current state out, fix it in the tool the registrar already knows, put it back.

The account, and the password that must not exist

A student record in this platform carries schooling only. Names, sex, date of birth and phone number live on Utilisateur, because a student is first of all a person with an account. So the import has to create accounts — and the moment it does, it faces a question with exactly one right answer.

A password set here would be either known to everyone — hence useless — or unknown to the person concerned, who could not get in.

set_unusable_password(), and doit_changer_mot_de_passe=True. There is no third option that is not worse: every scheme involving a generated password to be distributed later ends with a spreadsheet of passwords in someone’s inbox.

Then the homonyms. Many rows in an old class list carry no email address at all, so the username has to be composed — and composed usernames collide, because schools have several students with the same name.

KEÏTA Moussa becomes moussa.keita, not moussa.keïta — the reason for the combining-mark filter is in the comment: this string will be typed on a keyboard by the person it belongs to. The second Moussa Keïta becomes moussa.keita-2. Not elegant; deterministic, and it does not fail an import over a coincidence of birth.

The teacher import makes the opposite identity choice on purpose, for the same class of reason:

Inventing a staff ID would make the second import of the same corrected file fail. An identity key you generate is not a key. It has to be something the file already carries, or replay breaks.

What we would carry to the next one

Five things, in the order they matter:

1. The preview must be the execution, rolled back. Any other preview is a different program telling you about this one. dry_run plus a transaction is the cheap, honest version.
2. Be generous about form, rigid about identity. Accept 2ème; refuse ZZ, and put the valid values in the refusal.
3. Number rows the way the reader’s spreadsheet numbers them. rang + 2.
4. Re-uploading a corrected file is the normal gesture. Design for it: stable identity keys taken from the file, no silent overwrites, an export whose columns re-import.
5. Say what the failure mode is, in the code. The comments quoted above are not documentation of behaviour — they are the reason the behaviour is what it is, kept next to it so the next person does not “simplify” it back.

The whole thing is about 1 900 lines. The parsing is maybe two hundred of them. The rest is the promise that the report is true.