An Excel import that cannot destroy data
Mehdi Messaoudene1 min readDesktop applications

The real risk of an Excel import is not reading the file, it is ruining the catalogue with it. Bio Réactifs stacks three safeguards: a row-by-row preview before writing, a single transaction that ignores empty cells, and a snapshot of the database before every import.
- A four-step wizard: choose, map, preview, run.
- An empty cell never overwrites a value already in the database.
- A snapshot of the database is taken before every import: a failure leaves it intact.
Where is the real problem?
Reading an Excel file is the easy part. The heart of the problem is not ruining the catalogue with it. Supplier workbooks are incomplete, their columns change, and an empty cell does not mean "erase the value".
Three safeguards stack up in Bio Réactifs, the desktop application that holds the whole catalogue locally.
First safeguard: the preview
The import wizard moves in four steps: choose the file, map the columns, preview, run. The preview classifies each row as new, changed, unchanged or in error, and shows a field-by-field before/after diff for changes. Nothing is written until you confirm.
Second safeguard: the write
The whole import runs in a single transaction. Writes use an UPSERT where each field is wrapped: an empty cell in the file never replaces a value already present.
ON CONFLICT(reference) DO UPDATE SET
fournisseur = COALESCE(NULLIF(excluded.fournisseur, ''), produits.fournisseur),
...
Third safeguard: the backup
A snapshot of the database is taken before every import. If an exception occurs, the database stays intact and the backup path is returned to the user.
Mapping tolerates approximation
Supplier workbooks never have the same headers twice: "Réf.", "reference", "REF PRODUIT". Rather than imposing a template, the mapping combines a synonym dictionary with a Levenshtein distance, with accents and case normalised. The validated mapping is remembered: the second import from the same supplier is immediate. Only one constraint stays hard: there must be exactly one reference column.
Frequently asked questions
What happens if a cell in the file is empty?
It is ignored. The write uses an UPSERT where each field is wrapped so an empty value never replaces the existing one.
Can the same import be replayed?
Yes, that is exactly what this rule allows: replaying the import without losing corrections already made by hand.
Cet article existe aussi en anglais
Discussion
Comments use a GitHub account.


