Moving from Spreadsheets to Real Software: Why the Import Report Matters More Than the Import
When a business outgrows its spreadsheets, the risky moment is not choosing the new software; it is the day the old sheets are poured into it. A date of 12/05/2011 can silently become 5 December instead of 12 May. A parent whose phone number was typed three ways can become three records, and receive three copies of every message. A preview of the import can use up invoice numbers that can never be given back. None of these shows up as an error, which is exactly the problem. We built importers for our own software and found each of these in audits. What follows is what goes wrong, and why the import report, the page that tells you what happened, matters more than the import itself.
Key takeaways
- The risky moment in leaving spreadsheets is the import, and its report is the real interface.
- A plain database date conversion flips day-first dates in silence for every row with a day of 12 or less; parse with a stated convention and show dates back in words.
- A dry run must consume nothing: no invoice numbers, no messages, no counters.
- Count already-existing rows separately, and normalise matching keys, but never normalise a guess.
- Keep originals, reconcile counts and totals after the run, and migrate in batches.
The import report is the real interface
Whoever is migrating an 800-row spreadsheet will not get it right the first time. They will fix it one message at a time: this date is wrong, that row is missing a number, these two people are the same. The paste box is where the data goes in, but the report is where the work actually happens, so it is the part to inspect when you judge a migration tool or a developer's plan.
A good report says, in plain words, how many rows were new, how many already existed, how many were refused and why, and which cell each refusal refers to. A bad one says imported successfully and leaves you to discover the damage months later.
Problem 1: dates that flip in silence
Many databases read a date such as 12/05/2011 with the month first, so it is stored as 5 December. Every Indian spreadsheet writes the day first, so the same text means 12 May. A plain conversion therefore corrupts every row whose day is 12 or less, without any message at all, while the rows whose day is 13 or more, such as 25/05/2011, fail loudly because there is no 25th month.
That mix is the trap. The operator fixes the visible failures, sees zero errors, and ships the corruption. In a school's admission data it means wrong dates of birth flowing into certificates and age-based rules, discovered years later. We found this in an importer we had written ourselves, during an audit before anyone had used it, so nothing was corrupted in practice. It could easily have been otherwise.
The fix has four parts. Never rely on the database's default conversion. Parse with a stated convention, day first for India. Refuse what the convention cannot explain, such as 13/25/2012 or 31/02/2011, and say so on that row instead of quietly trying the other order. And show the result back in words before anything is saved: 12/05/2012 becomes 12 May 2012. That is a question a person can answer at a glance, and a silent conversion cannot be caught at all.
| The sheet says | It means | A plain conversion stores |
|---|---|---|
| 12/05/2011 | 12 May 2011 | 5 December 2011, silently |
| 03/11/2012 | 3 November 2012 | 11 March 2012, silently |
| 25/05/2011 | 25 May 2011 | An error: the row fails visibly |
What a plain month-first conversion does to a day-first sheet
Problem 2: a preview that is not harmless
Most good importers offer a dry run: show me what would happen. The rule is that a dry run must consume nothing. It must not take numbers from a series that is meant to have no gaps, such as invoice or receipt numbers; must not send a message; and must not change a counter. A gap in an invoice series is permanent, and an auditor can see it.
The safest design is one function with a commit flag rather than two separate ones, so the preview can never drift from the real run, and everything that takes a number or sends a message sits behind the flag. Then test it: note the counters, run a preview, and confirm they have not moved.
Problem 3: already there is an outcome, not an error
Re-pasting a corrected sheet is the normal way people work. A rows-already-exist result is therefore not a failure, and a report that counts it as one teaches people to ignore the error count, which is the one number that matters. Count it separately.
Where rows have no natural identifier, say so and warn that pasting again will create duplicates, rather than inventing a name match that quietly merges two real people who happen to share a name.
Problem 4: one person, three records
Matching on raw text fails. One parent written as +91 98765 43210, 098765-43210 and 9876543210 becomes three records and receives three copies of every message. The fix is to normalise the matching key, here the phone number, to one canonical form before comparing.
The opposite mistake is just as real: do not normalise a coin flip. Numeric gender codes mean opposite things in different systems, so a conversion that guesses will be wrong for half the rows. A blank beats a guess, because a blank is visible and a wrong value is not.
A checklist before you migrate
You do not need to understand the code to protect yourself. These are the questions to put to whoever is doing the migration.
- Keep a read-only copy of every original sheet, untouched.
- Agree the date convention in writing, day first or month first, and ask to see dates shown back in words.
- Ask what identifies a person or an item, and what happens if the same sheet is pasted twice.
- Insist on a dry run that consumes nothing, and ask how that is proved.
- Read the report: new, already existing, refused with reasons, and the cell each refusal points to.
- After the real run, reconcile: the row count and the totals in the new system against the sheet.
- Migrate in batches, one branch or one year at a time, not all at once.
Where this fits in your plans
Leaving spreadsheets is usually the first step of replacing a process, not the whole project. If you are weighing whether to build, custom development versus no-code covers the choice, whether a small business needs a CRM and when a growing business needs an ERP cover two common destinations, and how to write a software brief covers asking for it. If you have a stalled system or a tangle of sheets you want to retire, our growth and strategy service includes migrations like this, scoped in writing, and you can send us the details.
An importer that says imported successfully has told you nothing. The report is the product.
Frequently asked questions
Why do dates change when I import an Excel sheet into software?
Many databases read dates with the month first, so 12/05/2011 is stored as 5 December while the sheet meant 12 May. Rows whose day is 12 or less are changed silently; the others fail visibly. Use an importer that parses with a stated day-first convention and shows each date back in words.
How do I migrate from Excel to software safely?
Keep untouched copies of the sheets, agree the date convention, decide what identifies each record, run a dry run that consumes nothing, read the report line by line, reconcile counts and totals afterwards, and migrate in batches.
What should a data import report show?
How many rows were new, how many already existed, how many were refused and the reason for each, and the cell each refusal refers to, in plain words.
What is a dry run in a data import?
A preview of what the import would do without doing it. A correct dry run consumes nothing: it does not use up numbers from gapless series such as invoice numbers, send messages or change counters.
Why does my import create duplicate customers?
Usually because matching used raw text, so the same phone number written in different ways became several records. The importer should normalise the matching key before comparing, and warn when rows have no natural identifier.
Should an importer guess when data is ambiguous?
No. If a value cannot be understood, such as a date that does not exist or a code that means different things in different systems, leave it blank and say so. A visible blank is better than an invisible wrong value.
Have a project in mind?
We design, build, and ship software end-to-end — with a fixed, written quote after a free scoping call.