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.

"""
L'exécution d'un import — la pièce qui manquait. [...] Les cinq types d'import
déclarés n'avaient jamais créé une seule ligne dans les tables métier. Le
rapport disait vrai — il ne disait simplement rien de ce qui serait importé,
puisque rien ne l'était.
"""

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:

def test_l_execution_cree_vraiment_les_etudiants(self):
    """Ce que l'application ne faisait pas du tout."""

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:

def _passer(operation, jeu, *, essai):
    """
    Passe le jeu de données dans la ressource. Rend `(resultat, lignes)`.

    Le même chemin dans les deux cas : un essai qui suivrait un autre chemin
    que l'exécution ne prouverait rien de ce qui se passera.
    """
    ressource = ressource_pour(operation.type_import)
    exiger_present(ressource, f"aucune ressource pour {operation.type_import}")

    ressource.maj_existants = operation.maj_existants

    resultat = ressource.import_data(
        jeu, dry_run=essai, raise_errors=False, collect_failed_rows=False,
    )
    return resultat, ressource

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:

class RessourceENI(resources.ModelResource):
    """
    `use_transactions` n'est pas décoratif. L'essai à blanc écrit réellement
    dans la base avant de tout défaire ; sans transaction, un import « pour
    voir » créerait les comptes et les laisserait là.
    """

    class Meta:
        use_transactions = True
        skip_unchanged = True
        report_skipped = True

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:

def test_l_essai_n_ecrit_rien(self):
    """
    Le point sur lequel tout repose. Un essai qui laisserait des traces
    rendrait la relecture impossible : on relirait un état déjà changé.
    """
    avant = Etudiant.objects.count(), Utilisateur.objects.count()
    self._depot()
    self.assertEqual((Etudiant.objects.count(), Utilisateur.objects.count()), avant)

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.

#: Encodages essayés pour un fichier texte, dans l'ordre. `utf-8-sig` d'abord :
#: il retire le BOM qu'Excel ajoute, lequel collerait sinon au nom de la
#: première colonne — et « matricule » n'est pas « matricule ».
ENCODAGES = ("utf-8-sig", "cp1252", "latin-1")

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:

def _separateur(texte):
    """
    Le séparateur du CSV, déduit de la première ligne.

    Les tableurs francophones écrivent `;` — la virgule y est le séparateur
    décimal —, les exports anglophones `,`. Se tromper donne une seule colonne
    portant toute la ligne, et un rapport qui parle de colonnes manquantes sans
    que l'agent comprenne pourquoi.
    """
    premiere = texte.split("\n", 1)[0]
    return max((";", ",", "\t"), key=premiere.count)

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:

"""
**On accepte ce qu'écrivent les gens, pas ce qu'attend la base.** [...]
Refuser une liste parce qu'un secrétariat a écrit « 2ème » ferait perdre plus
de temps que la ressaisie.

**Rien n'est deviné en silence.** Ce qui ne se résout pas est refusé avec le
motif, et la ligne paraît au rapport. Un département inconnu créé à la volée
polluerait le référentiel pour des années.
"""

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:

class DepartementWidget(Widget):
    """
    Un département par son sigle ou par son nom.

    Jamais créé à la volée : un sigle mal saisi ferait naître un département
    fantôme qui resterait des années dans le référentiel, et l'on ne saurait
    plus lequel est le bon.
    """

    def clean(self, value, row=None, **kwargs):
        ...
        if trouve is None:
            connus = ", ".join(
                Departement.objects.values_list("sigle", flat=True)[:12]
            )
            raise Refus(
                _("Département « %(valeur)s » inconnu. Sigles en usage : %(connus)s.")
                % {"valeur": texte, "connus": connus}
            )
        return trouve

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:

def test_le_refus_dit_pourquoi_et_comment_corriger(self):
    """Un motif que l'agent ne peut pas suivre ne vaut pas mieux que rien."""
    fautive = LigneImport.objects.get(
        import_lie=operation, etat=LigneImport.Etat.ERREUR
    )
    self.assertEqual(fautive.numero, 4, "le numéro est celui du tableur")
    self.assertIn("ZZ", fautive.message)
    self.assertIn("GC", fautive.message, "les sigles en usage sont rappelés")

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.

    # « 2e année » existe dans chaque cycle : sans précision on prendrait
    # celle du premier venu, et l'étudiant se retrouverait en licence
    # professionnelle sans que personne ne s'en aperçoive. L'ordre de
    # recherche est donc explicite, du plus précis au plus général.
    if cycle:      # une colonne « cycle » tranche
        ...
    if departement:  # l'année propre au département, s'il en déclare une
        ...
    return (
        annees.filter(cycle__code=CYCLE_PAR_DEFAUT, departement__isnull=True).first()
        or annees.filter(cycle__code=CYCLE_PAR_DEFAUT).first()
        or annees.first()
    )

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.

#: Le cycle retenu quand la ligne ne le dit pas. « 2e année » existe dans les
#: cinq cycles ; sans ce choix affiché, l'import placerait l'étudiant dans le
#: premier venu.
CYCLE_PAR_DEFAUT = "ingenieur"

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:

# On repasse à blanc pour savoir quelles lignes tiennent : la bibliothèque
# défait tout dès qu'une ligne échoue, et l'on ne peut donc pas écarter les
# fautives en cours de route.
essai, _r = _passer(operation, jeu, essai=True)
fautives = {
    rang for rang, ligne in enumerate(essai.rows)
    if _etat_de(ligne) == LigneImport.Etat.ERREUR
}

retenu = tablib.Dataset(headers=list(jeu.headers))
for rang, ligne in enumerate(jeu):
    if rang not in fautives:
        retenu.append(ligne)

with transaction.atomic(), sans_journal():
    resultat, _ressource = _passer(operation, retenu, essai=False)

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:

operation.message = _(
    "%(n)d ligne(s) en erreur : rien n'a été importé. Corrigez le fichier, "
    "ou cochez « importer les lignes valides malgré les erreurs »."
) % {"n": len(fautives)}

Silencing the audit log on purpose

One line in that block deserves its own section:

# Le journal d'audit suit les écritures des modèles de scolarité : un
# import de mille étudiants y laisserait mille entrées, et le journal
# deviendrait illisible le jour même. Il en laisse une : celle de l'import.
with transaction.atomic(), sans_journal():

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:

consigner(
    JournalEntree.Action.IMPORT,
    acteur=acteur or operation.agent,
    cible=operation,
    objet=_("%(type)s — %(fichier)s") % {
        "type": operation.get_type_import_display(),
        "fichier": operation.nom_fichier,
    },
    detail={
        "importees": operation.importees,
        "rejetees": operation.rejetees,
        "total": operation.total,
    },
)

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:

class Meta(RessourceENI.Meta):
    model = Etudiant
    import_id_fields = ("matricule",)

And no clobbering, which needs a decision:

def skip_row(self, instance, original, row, import_validation_errors=None):
    """
    Laisse tranquille ce qui existe déjà, sauf demande expresse.

    Sans quoi redéposer un fichier pour rattraper trois lignes réécrirait
    les mille autres — et effacerait au passage ce qu'un agent a corrigé à
    la main depuis l'import précédent.
    """
    if original is not None and getattr(original, "pk", None) and not self.maj_existants:
        return True
    return super().skip_row(instance, original, row, import_validation_errors)

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.

def test_une_fiche_connue_n_est_pas_ecrasee_sans_qu_on_le_demande(self):
    execution.executer(self._depot(ignorer_erreurs=True))
    Etudiant.objects.filter(matricule="ENI24-0001").update(promotion="corrigé")

    execution.executer(self._depot(ignorer_erreurs=True))
    self.assertEqual(
        Etudiant.objects.get(matricule="ENI24-0001").promotion, "corrigé"
    )

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:

def test_l_export_se_redepose(self):
    """
    Un export dont les colonnes ne se redéposent pas n'est qu'une
    impression : c'est ce qui rend la correction en masse possible.
    """
    jeu = tablib.Dataset().load(reponse.content, "xlsx")
    self.assertEqual(
        set(EtudiantRessource.Meta.fields) - set(jeu.headers), set()
    )

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.

def _compte(*, email, nom, prenom, role, sexe="", telephone="", naissance=None):
    """
    Sans mot de passe utilisable. Un mot de passe posé ici serait ou bien
    connu de tous — donc inutile — ou bien inconnu de l'intéressé, qui ne
    pourrait pas entrer. La personne le pose elle-même par « mot de passe
    oublié », depuis l'adresse qui figure sur cette ligne.
    """

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.

    # Sans courriel, l'identifiant est composé — et débarrassé de ses accents :
    # c'est ce que la personne tapera au clavier pour entrer.
    compose = unicodedata.normalize("NFKD", f"{prenom}.{nom}".lower())
    compose = "".join(c for c in compose if not unicodedata.combining(c))
    identifiant = email or compose.replace(" ", "-")
    base, suffixe = identifiant, 1
    while Utilisateur.objects.filter(username=identifiant).exists():
        suffixe += 1
        identifiant = f"{base}-{suffixe}"

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:

"""
Un enseignant existe à l'annuaire sans compte — c'est le cas des vacataires
— et le compte ne se crée donc que si un courriel est donné. Le nom et le
prénom réunis identifient la ligne : l'école n'a pas de matricule
d'enseignant, et en inventer un ferait échouer le deuxième import du même
fichier corrigé.
"""

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.