Automated data onboarding without duplicate records

Automated data onboarding is the import that happens in the first weeks of a new system, when supplier and customer records arrive as exports, price lists and address books from wherever they were kept until then. It works when the import is a repeatable program: a reconciliation total, and a queue for rows that fail. It fails when somebody maps columns once in a spreadsheet and calls the file imported.

What do the source files actually look like?

Worse than the sample you were shown. A supplier list with a merged header row, a customer export from a phone with no column names at all, an accounting dump with totals sitting inside the data, and a price list sent as a PDF with the discount in a footnote.

Expect the header on the third row, columns named Unnamed: 4, dates as text, phone numbers with the leading zero gone, and a totals row mid-table. None of that is carelessness: the file worked when a person read it.

Ask for one real file from every source, not a tidy template, and ask who maintains it: they know which column is trusted.

How do you spot the same supplier typed three ways?

Normalise before comparing, and compare on more than one signal. The legal form is noise: SARL, EURL, SPA and their Arabic equivalents attach to the same company, and so do case, spacing, accents, "et fils", "frères" and transliteration.

Signal	Good for	Where it misleads
Registration number	merging with certainty	missing from many rows
Normalised phone	customer records	shared by a family, or a shop's second line
Name similarity	proposing candidates	brothers, branches, a renamed shop

Run the comparison in tiers: an exact match on a registration number or a normalised phone is a merge; a name score only proposes one for a person to confirm. Never merge on the name alone: the same name exists in different wilayas, and brothers trade side by side.

What has to be normalised before anything is written?

Units and identifiers first, with the original kept beside the parsed value so both survive.

• Units. A carton belongs to the supplier's packaging, not to your stock. Store quantity in the base unit and keep the text that was sent.
• Identifiers. The supplier's own code stays visible because their invoices quote it; your key is a separate field, never an overwrite.
• Phones. One format, country code included, leading zero restored, stored as text.
• Addresses. Wilaya and commune as separate fields; the free-text line stays as the customer wrote it.
• Money. One currency field, decimals as numbers, and a date beside every balance. A balance with no date gives an ageing report that is wrong without looking wrong.

What reconciliation total proves the import?

The one agreed before the import and checkable afterwards by hand. Pair a count with a sum, because a row count matches while a column is shifted, and a sum matches while a missing row cancels another.

The figures worth agreeing: rows in the source, staged, accepted and rejected; the sum of opening balances; duplicates merged; records with no identifier; the largest, smallest and any negative balance. Print the source page when the copy is frozen and reconcile against that page, not the live file. If one figure differs, stop — importing the rest and reconciling later destroys the only signal that says which half is wrong.

What happens to the rows that fail?

Every row lands in one of three buckets, none of them silent.

Accepted rows are written with the identifier from the source kept on the record. Rejected rows have an error the file can fix — a missing name, a date that will not parse — and come back as a file with the original row untouched and a column saying why. Parked rows need a decision the file cannot make: a customer who may match an existing record, a supplier with no identifier. They wait for a named person, not for the next import.

The reject file is the work queue. A queue that never shrinks means the mapping is wrong, not that the data is bad.

Why must the import be re-runnable?

Because the file changes while you are importing it. The supplier list is edited every morning by whoever still uses it, the first run is wrong in a way you find out later, and the next file arrives with different columns.

An import that runs once — a macro on somebody's laptop — turns each into manual work, and manual work at that scale produces the duplicates you just cleaned. A re-runnable import is keyed on a natural key, so a second run updates instead of duplicating; it logs what it did; it can be rolled back; and it takes the same arguments next month. Run it under a named account, not a shared login (OWASP authentication guidance).

What arrives this way keeps its provenance: which file, which run, which mapping version (W3C Data on the Web Best Practices).

What order do you do this in?

1. Collect one real file from every source, and name the person who maintains it.
2. Write the target record and its natural key, for suppliers and customers separately.
3. Freeze a copy of each source, print the reconciliation page, and work on the copy only.
4. Build the importer as parse, normalise, match, stage, report — with no direct writes.
5. Dry-run into staging, reconcile, then read the reject file before going further.
6. Merge duplicates from a proposed list a person approves, never from a script alone.
7. Load, reconcile again, and keep the run log and the reject file.
8. Run it a second time on the next file, to prove it repeats.

Where this goes wrong

A macro on one laptop. The import works until the person who wrote it leaves, and nobody can run it when the supplier list changes.

The dropped leading zero. Phone numbers exported as numbers lose a digit, the match fails, and the customer is created again under a variant of their name.

Merged cells and totals rows. They arrive as records or as Unnamed columns, and reconciliation approves them because they count as rows.

Merging by name. Two real suppliers become one, their balances are summed, and an invoice is paid to the wrong party. Nothing throws an error.

The second import that cannot happen. Onboarding continues after go-live, the file keeps changing, and the only importer was a one-off built for the first week.

What this does not cover

This is not about choosing the system or designing its schema, and not about what you must retain in the old one for accounting reasons. It does not cover cleaning records you do not own: a supplier's list is theirs, and the address is what the customer told you. It assumes somebody can answer whether two records are the same company, because no similarity score settles that.

We write the importer and the reconciliation that proves it, so the next file costs a run rather than a project.