Short answer. Migrating your data is not an import the day before launch: it is a six-week workstream run alongside the building of the application. It starts with an inventory of sources and of what prevails, then a signed mapping table: where each field comes from, by which rule, and what is left behind. Cleaning takes longer than everything else: names, phones, dates and amounts normalised, duplicates merged by rule, and the values the spreadsheet damaged, a date turned number, a twenty-digit identifier truncated to fifteen, asked again and never guessed. Everything is then dry-run loaded three times into a copy, with a rejection report. The proof that nothing is lost is a reconciliation: number of customers, open invoices, total balance, stock, equal in the old and the new. The switch-over happens in one weekend, old system read-only, with a dated rollback. Only what is still alive is migrated; the rest is archived.
This guide is for the manager in Algeria whose business lives in Excel workbooks and a ten-year-old system, the founder settled in France taking over a family business and its files, and anyone comparing quotes where “data migration” is one line. It does not cover the permanent connection between two systems, described in the integration guide; it describes the passage, done once, from the old to the new.
What the figures say
A spreadsheet stores what is typed, and it has written limits. Microsoft’s Excel specifications page gives a sheet of 1,048,576 rows by 16,384 columns, a numeric precision of fifteen digits, and dates calculated from 1 January 1900. Those three lines explain three migration failures. Dates are numbers: 45123 is 16 July 2023, and a column exported without its format arrives as a series of numbers. An identifier longer than fifteen digits entered as a number loses its last digits, replaced by zeros: that is the case of a twenty-digit bank identifier, and no rule gets them back. And a file exceeding a million rows was split into several, somewhere.
Dates hold another documented surprise. Microsoft explains that Excel wrongly assumes 1900 is a leap year, for compatibility with the Lotus 1-2-3 spreadsheet, and that fixing this would shift almost every date in existing workbooks by one day; there is also a second date system starting in 1904. What this imposes: date conversion uses the spreadsheet’s rule, not a naive subtraction, and is checked on known dates, the company’s creation date, the first invoice, before being applied to a hundred thousand rows.
The load itself is the easy part. The PostgreSQL documentation recommends loading rows with the bulk copy command rather than a series of inserts, because it “incurs significantly less overhead for large data loads”, creating indexes after the load, and running table analysis afterwards; a load in a single transaction has another advantage, written on the same page: if one row fails, everything is rolled back, and you are not “stuck with partially loaded data”. What this imposes: a hundred thousand invoices load in minutes, and all the time is elsewhere, in cleaning and reconciliation. What we did not find: a statistic on the state of Algerian companies’ files; what we see is that a ten-year customer file usually contains a few per cent of duplicates, and we say so as an observation.
Two Excel rows become one customer record
Migration is not understood on a database diagram but on two rows of a real file.
The name: “sarl el baraka” with a trailing space and “EL-BARAKA (SARL)” are the same customer; case, spaces and hyphens are normalised, and the legal form goes into its own field. The phone: two numbers in the same cell, one with the zero, one without; one number per field, in international format with the +213 code, the one that will serve for the login described in the OTP guide. The date: 45123 in one row and 16/07/23 in the other are the same date; the first is converted from the spreadsheet serial number, the second read with the format of the workstation that entered it, and when day and month can swap, the row is listed.
The bank identifier: twenty digits entered as a number, shown in scientific notation, the last five lost; the second row has it as text but truncated; it is flagged “to ask the customer again” and the record goes in without it. No rule rebuilds a lost digit, and an app that guesses a bank identifier is an app that sends a transfer to the wrong account. The town: “blida” and “Blida ctr” are attached to wilaya code 09. The merge: the two rows share the same phone and a close name; they become one record, their balances add up, and the two original rows stay attached to the record so the merge can be undone.
Data migration, in six steps
Behind those two rows, six steps, and the third takes longer than the other five.
The inventory lists every source, who keeps it, since when, and above all what prevails when two sources disagree: the accountant’s file or the sales rep’s. The mapping is a table, column by column: where each field of the new application comes from, by which transformation rule, and what is left behind; it is signed by the manager, and it also serves the developers, who discover in it the fields the business really uses. The cleaning applies the rules, detects duplicates, and produces the list of what cannot be repaired and must be asked again; that list goes to the teams from week two, because calling two hundred customers back takes time.
The dry-run load loads everything into a copy of the new application, times the operation, and produces a report: so many rows in, so many rejected, and why. It is done three times: the first reveals forgotten rules, the second fixes them, the third is the dress rehearsal of the switch-over. The reconciliation compares totals between old and new. The switch-over follows a written hour-by-hour run sheet, rehearsed, with a rollback. All of it runs while the application is being built, with the batch method described in the delivery guide.
«No rule rebuilds a lost digit: what the spreadsheet damaged is asked again, not guessed.»
The switch-over in one weekend
The switch-over is the moment the company changes systems; it fits in a weekend because everything that could fail has already failed three times.
Friday 6 pm, the old system becomes read-only: you can consult, you no longer enter; that is what guarantees the figures compared the next day do not move. Saturday, the last load, identical to the third dry run, then the reconciliation. Saturday evening, four figures are read on both sides: the number of active customers, open invoices one by one, total customer balance to the dinar, stock references with their quantities per depot. They are equal, or we do not open on Monday. Merged duplicates are not a gap: they are listed, with the original rows.
Monday 8 am, the new application opens, and the old one stays consultable. Until Monday noon, rollback is possible: the old system goes back to write mode and the morning is re-entered; past that hour, new entries are too many and corrections go forward. That deadline is written before the weekend, not decided in a rush. The following week, teams report the gaps they see, a customer not found, a disputed balance, and each is traced to its original row; the dated backup of the old system is kept, as the hosting guide provides.
Six sources, how they are retrieved and what is checked
Six sources we meet, and for each the way to read it and what is checked.
Excel files are read sheet by sheet, discarding merged cells, column-bottom totals and title rows posing as data; checks cover dates as numbers, long identifiers, leading zeros and Arabic encoding. Old software with a database is read by export or read-only access, never writing to it; it holds fields diverted from their use, the “fax” field containing the sales rep’s name, and codes with no label. Software with no export is migrated through its PDF printouts, read back as in the document reading guide, with each report’s total as the control figure.
Paper and notebooks are photographed and read, or entered by hand, with double entry on a sample and balances confirmed with the customers concerned. Several files per branch require building a common key, phone and identifier, because the same customer appears under three names. Messages and photos are migrated only for what still has an effect, an open balance, an undelivered order. And not everything is migrated: ten years of delivered and paid orders stay searchable in an archive; what must enter clean is what is still alive, customers, products, balances, stock, open orders.
«The proof that nothing is lost is four totals read on Saturday evening, equal on both sides.»
Six migration traps
Six traps we see on the migrations we rescue, and what the method does against each.
The duplicate: the same customer exists three times and their balance is split; key on phone and identifier, names compared with tolerance, merge by rule, list validated by the manager. The date turned number: conversion from the spreadsheet serial number, and dates whose day and month can swap are listed rather than guessed. The damaged identifier: detected by its length and trailing zeros, asked from the customer again, never rebuilt.
The balance that does not add up: the total of customer balances differs from accounting even before migration; the gap is explained line by line, or the switch-over waits, because an imported gap becomes an unexplainable gap. Unreadable Arabic: names turn into question marks; encoding is detected file by file, converted, and checked by eye on a sample. The switch with no return: a figure is wrong on Monday and the old system was changed in the meantime; read-only, dated backup, rollback written with its deadline. The first three come from the spreadsheet itself; the last three, from the method; none shows before the first dry-run load.
Seen elsewhere: Excel’s limits, PostgreSQL, and what transposes
Two sources say the essentials. Excel’s specifications say a number has only fifteen digits of precision and dates are numbers counted from 1900, with one leap year too many; what transposes is that three of the six traps are not entry errors but the spreadsheet’s normal behaviour, and they are handled by rules, not by blaming the file on the team. The PostgreSQL documentation says bulk loading is fast and a single transaction rolls everything back on failure; what transposes is that you can load three times without fear, and that a half-done load does not exist.
What these sources do not say: which sources you have, which prevails, how many duplicates, and which figures your accountant wants to see equal on Saturday evening; the project’s first week answers that, with the files open on the table.
The rules of a migration that loses nothing
Six rules, written in the first week. Migration starts with the project, not at the end. A signed mapping table: every field has a source and a rule, or is left behind in writing. We never guess: what is damaged is listed and asked again. Three dry-run loads, timed, with a rejection report. The reconciliation prevails: equal totals on both sides, chosen with the accountant, or the switch-over waits. A written rollback, with its deadline, and the old system read-only.
A seventh rule concerns the quote: “data migration” is not a requirement, it is a line; the requirement reads “inventory of sources, validated mapping table, cleaning and duplicate-merging rules, list of values to ask again, three dry-run loads with rejection report, reconciliation on such totals, switch-over in one weekend with run sheet and rollback, searchable archive of the history not migrated”, and it is checked as the specification guide recommends. A quote that prices “an Excel import” has priced the load, not the migration.
Six companies, and what is migrated first
Six companies we often support, and for each what is migrated first.
- The wholesaler and the distributor, before opening the portal described in the dedicated guide. Customers with their balance and limit, catalogue with per-customer prices, stock per depot; order history in the archive.
- The clinic and the practice, described in the practice guide. Patients deduplicated on phone and date of birth, upcoming appointments, open treatment plans; old files scanned and attached.
- The real-estate agency and the owner’s space, described in the dedicated guide. Properties, owners, current leases, unpaid rents; past receipts in the archive.
- The private school. Pupils and parents with normalised phones, this year’s enrolments, instalments still due.
- The SaaS vendor, described in the multi-tenant guide. An import tool per customer, with the same mapping table and the same rejection report, because every new customer arrives with their files.
- The business taken over from France. Everything that lives in the previous manager’s head and workbooks: migration is also when you discover what the company knows about itself, and the dashboard described in the dedicated guide comes out of it.
The 2027 calendar
The calendar is that of a complete migration in six weeks, run while the application is being built.
Week 0 to 1 does the inventory and mapping: all sources, what prevails, the table column by column, what is migrated, archived or left; signed. Weeks 1 to 3 write the cleaning: normalisation rules, duplicate detection, and the list of values to ask again sent to the teams as soon as it exists. Week 3 to 4 does the first dry-run load, with its rejection report and a first reconciliation that is almost always wrong, which is its job.
Week 4 to 5 does the second and third: rules corrected, duplicates validated one by one by the manager where the rule hesitates, duration measured, reconciliation right. Week 5 to 6 is the switch-over and follow-up: the weekend, the opening, and a week tracing every reported gap to its original row. Cleaned names and products also serve search, whose normalisation is described in the search guide.
What makes a migration fail
The migrations we rescue almost always failed for one of these reasons.
- The import the day before. Discovered in the last week, done once, on a Friday evening.
- Migrating everything. Ten years of history imported with its errors, instead of what is still alive.
- Guessing. An identifier completed, a date assumed; the error is found at the first transfer.
- No rule on duplicates. Merged by hand, differently by each person.
- No reconciliation. “It looks fine”; the total balance was never compared.
- The old system still open. Entries on both sides during the switch-over.
- No rollback. Monday morning, there is no way back.
Frequently asked questions
The questions we receive from managers, accountants and founders.
- Can our Excel files be brought into the new application?
- Yes, and it is a workstream of its own: inventory of files, column-by-column mapping table, cleaning, three dry-run loads and a reconciliation of totals. What is still alive is migrated, customers, products, balances, stock, open orders; old history stays searchable in an archive rather than being imported with its errors.
- What happens to duplicates?
- They are detected by a key, phone and identifier, then by a tolerant comparison of names, and merged by a written rule; where the rule hesitates, the list is validated by the manager. Balances add up, the original rows stay attached to the record, and every merge can be undone.
- How do you prove nothing is lost?
- By a reconciliation read on the switch-over Saturday: totals chosen with your accountant, number of active customers, open invoices one by one, total balance to the dinar, stock per depot, equal in the old system and the new. If one figure differs, opening is postponed; it is not an impression, it is an equality.
- Why are our dates and numbers wrong in the export?
- Because the spreadsheet stores dates as numbers counted from 1900 and keeps only fifteen digits of precision for a number: 45123 is a date, and a twenty-digit identifier entered as a number has lost its last digits. Dates are converted with the spreadsheet’s rule; a lost digit is not found again, it is asked again.
- Does the business stop during the switch-over?
- No, if it is prepared: the old system becomes read-only on Friday evening, loading and reconciliation happen over the weekend, and the new application opens Monday morning. A rollback stays possible until a deadline written in advance, and the old system stays consultable afterwards.
- How long to migrate the data?
- Six weeks, alongside the building of the application: one for inventory and mapping, two for cleaning, two for the three dry-run loads, one for the switch-over and follow-up. The list of values to ask again goes to the teams from week two, because it is what takes the most calendar time.