From Spreadsheets to a Custom System: What to Migrate First
Move one complete operational workflow first, with a named owner, agreed rules and a boundary you can test. Bring across the reference data and active records that workflow needs, rather than importing every workbook at once. Accept the first release only when the records reconcile, the right people can do the work and the team knows where new changes belong during the switch.
TL; DR
- Define the takeover scope. Code review, feature development and live support are separate assignments. Bringing in a new developer does not by itself justify a rebuild or platform move.
- Preserve a known version and its requirements. Record the deployed revision, essential user journeys, setup steps, accounts, database, uploaded files and external services.
- Keep the business in control of access. Give the developer individual access, retain billing and recovery control, and keep production credentials out of the handover document.
- Rehearse the transfer away from production. Have the developer build from a clean checkout, test allowed and forbidden actions, deploy a small change, and demonstrate rollback and recovery using test data.
- Close with evidence and named responsibilities. Record verified behaviour, defects and untested assumptions. Agree who approves releases, handles incidents and maintains the app before operational responsibility transfers.
Pick a complete workflow, not a workbook
The first migration should remove a specific dependency on spreadsheets in daily work. For example, suppose a service company records orders in a workbook, marks approvals with colours and sends a weekly export to accounting. Moving the order list without its approval rules or export leaves the team maintaining the old file as well.
A workable first scope might be order intake and status tracking for one branch: create an order, assign the customer, approve it, correct or cancel it, and produce the output accounting needs. Pricing experiments and long-term forecasts can remain in spreadsheets if they do not control that live process.
The boundary must make operational sense. A branch is not independent merely because it has its own tab. If all branches share stock allocation or a single approval queue, those dependencies belong in the first-scope decision.
Choose the first process with four tests
Use the following tests with the people doing the work. They are selection criteria, not a numerical score that makes every process comparable.
- There is a specific recurring problem. Record an actual manual step, correction or delay and the task it affects. A large file or an old-looking workbook is not enough reason to replace it.
- Someone can define the correct result. Name the process owner who can settle what an approved order, outstanding balance or completed job means. A developer should not decide those definitions from column names.
- The dependencies can be bounded. Identify incoming records, required reference lists and downstream outputs. Determine what can remain unchanged while this workflow moves.
- The result can be accepted and recovered. Choose representative transactions, the checks they must pass and a workable response if the first release fails. Include a real operator in that review, not only the person funding the project.
Hypothetical comparison. For the service company above, these candidates lead to different next checks. None is automatically the right first project.
| Candidate in the example | What makes it a useful first scope | What would make you postpone it |
|---|---|---|
| Order intake and status tracking for one branch | An operator owns the process, the order states are agreed, and an existing accounting tool can receive the required output. | Orders cannot be separated from a shared allocation process that nobody has mapped. |
| A company-wide historical dashboard | The reporting question is precise and all source definitions and refresh responsibilities are already agreed. | Different teams use different definitions of completed orders, so the dashboard would combine incompatible figures. |
| Every workbook in the department | The files genuinely form one small, inseparable workflow with known rules and dependencies. | The scope includes unrelated planning, active operations and archives merely because they share a folder. |
The Government Data Quality Framework takes a useful starting point: data quality depends on the users and purpose, and effort should focus on the data most critical to those needs. Apply that here by asking what the first workflow must trust, rather than cleaning every historical sheet before any useful work can begin.
Find the rules hiding around the data
Make an inventory of the files actually used, including local copies, hidden tabs, linked workbooks, imports and scripts. Name the authoritative source for each type of record. The newest file timestamp does not, by itself, resolve conflicting edits.
Preserve an unchanged snapshot of the source workbook and note when it was taken. Work on a copy for cleaning and mapping. Keep the native workbook as well as any export used for the trial, so the developer can inspect formulas and conventions that a plain list of values does not explain.
Ask an operator to walk through an ordinary order and an exception. What makes a row ready for approval? Who can change a price after approval? Does a blank cell mean unknown or zero? Which report is copied into another file, and who checks it? Write the answers as rules the new system must implement.
Separate mistakes from deliberate exceptions. A historical order may correctly retain an old price. Recalculating it with the current price list would make the numbers consistent with today's formula and wrong for that transaction. Preserve the accepted historical value where the business rule requires it.
Map meaning before importing columns
A migration mapping specifies the source, destination, conversion rule and handling of exceptions. It should also identify who approves an uncertain conversion. The table shows the decisions for an illustrative order workflow, not a ready-made schema for every business.
| Source field or convention | Target rule to agree | Check before accepting the import |
|---|---|---|
| Order reference such as 00127 | Preserve it as a text identifier. Include the source or branch when numbers are not globally unique. | References keep their significant characters and point to the intended order. |
| Customer name entered in different ways | Map each order to an approved customer identifier; review ambiguous matches. | Similar names have not merged different customers or created duplicate identities. |
| Date entered as 03/04/2026 | Resolve the source date convention before conversion. | Known examples match the intended calendar date; unresolved values are flagged. |
| A blank paid amount | Decide whether it means unknown, not entered or a genuine zero. | Unknown amounts have not become zero merely to satisfy a required field. |
| A total calculated by a formula | Define the currency, calculation and historical value that the process must preserve. | Approved old orders retain the correct amount even if today's pricing rule differs. |
| Cell colour used to mean approved | Replace the convention with an explicit status and permission to change it. | Operators agree what the colour meant, including exceptions and missing colours. |
| Several item rows with the same order number | Keep one order with its distinct line items, linked through identifiers. | Valid line items have not been deleted as duplicate orders. |
Identifiers deserve an early check. Microsoft documents Excel's automatic conversion of numerical text, including removal of leading zeros and loss of precision in long numbers. Import identifier columns as text where their characters matter. If the source has already lost information, check it against an authoritative record; do not invent missing digits.
Load or establish the reference records needed by the workflow, such as customers and branches, before linking their orders. Then load the orders and any separate line items. Keep a source-to-target mapping so a disputed record can be traced back to the source file and row.
The database can enforce part of this agreement. PostgreSQL documents unique identifiers and foreign-key constraints that keep references tied to existing records. A constraint can reject an order linked to a nonexistent customer. It cannot tell you that two valid customer records were matched to the wrong orders. That needs a business-level check.
Do not delete repeated rows merely because a tool labels them duplicates. First establish what one row represents. Two rows may describe different items in the same order, two revisions, or a true repeated entry. Those cases need different treatment.
Test permissions as part of the workflow
Write down who can view, create, change, approve and export each relevant record. Include boundaries between branches or customers where they matter. An import can preserve every value and still expose it to the wrong people.
Do not translate spreadsheet protection into an assumed security model. Google explicitly warns that protected sheets and ranges are not a security measure; people may still copy or export protected content. That does not make spreadsheets unsuitable for every task. It means the new system's confidentiality requirements need an explicit design.
Use normal test accounts to check the agreed permissions, including actions that must be refused. OWASP recommends validating permission rules on every request. A hidden button is not sufficient evidence that the underlying action is unavailable. Have the developer test the enforcement as well as the interface.
Reconcile a trial import before moving live work
Run the first import in a non-production environment. Use representative data under appropriate access controls, and prevent the trial from sending customer messages, creating live invoices or changing connected production systems. Make the import repeatable so corrections can be tested against the same source snapshot.
Require an outcome for every source row: imported, deliberately excluded, combined with another record under an approved rule, or rejected for investigation. Preserve the reason and the source reference. A progress bar reaching the end is not a reconciliation report.
Hypothetical trial. Suppose the selected source sheet is intended to contain one row per order and has 120 rows. Review establishes that 3 are true repeat copies and 2 are explicitly marked test orders. The importer accepts 110 rows and holds 5 because their customer identity is unresolved.
The row accounting is 120 = 110 imported + 3 repeated copies + 2 test orders + 5 unresolved rows. All source rows are accounted for, but the migration is not complete. After removing the reviewed copies and test orders, 115 real orders remain. Five are still missing from the new workflow.
If those five are active orders in the chosen scope, resolve their customer mappings and rerun the trial before switching that work. Do not hide them inside an acceptable error percentage. A deliberate scope exclusion would require a named person and a working place to handle those orders; it cannot mean leaving them unserved.
Then check more than row counts. Compare distinct order identifiers, customer relationships, statuses and the relevant totals, grouped by currency where necessary. Check a sample of histories and attachments against the original records. Two misplaced orders can leave a grand total unchanged, and a normalized system may legitimately have a different number of database rows from the workbook.
Have the operator complete the full workflow using the imported records. Can they correct an order, see the right status, find an old reference and produce the required downstream output? Keep a list of discrepancies with owners. Data reconciliation and usable daily work are separate acceptance conditions; both must pass.
Decide where new edits belong during the switch
Choose the transition method before the final import. For a workflow that can tolerate an agreed pause, stop edits to the relevant source, take the final snapshot, import and reconcile it, then direct new work to the new system. Rehearse that sequence and set conditions for abandoning the switch before the pause begins.
If work cannot pause, the plan must capture and reconcile records created or changed during the move. That is additional scope. Merely keeping both systems available does not explain how a correction entered in one reaches the other.
Define one authoritative place for each kind of live edit at each stage. A read-only comparison period can be useful. Having staff independently edit both versions without reconciliation creates a second source of disagreement instead of a controlled transition.
Recovery also needs to cover new data. Before allowing live work, decide how orders entered after the switch will be preserved if the team returns to the old process. Restoring an earlier file would omit them. State who can authorize the return, how differences are reconciled and what must be checked before work resumes.
Retire the old workflow only after an operating cycle
Name the cycle that will demonstrate the first scope: for example, from order intake through its next required accounting export. Include corrections and exceptions rather than declaring success after the first successful entry.
Agree acceptance conditions before that cycle starts: required orders are accounted for, access rules pass, downstream outputs are accepted, and the responsible operator can complete the work without maintaining an undocumented parallel workbook. Missing conditions become explicit work, not a reason to quietly keep both workflows forever.
Keep the old data accessible in an agreed archive with a known owner and access method. Decide its retention requirements with the responsible business team; this guide does not prescribe a retention period. Remove editing from the retired operational copy where appropriate, while retaining spreadsheets that still have a legitimate analytical role.
Expand the scope only after the first workflow is stable enough to support the next one. The next useful step may be a related approval or reporting process, not another batch of files. A successful first migration establishes how the team defines, checks and operates its data, as well as where that data is stored.
About Caemcore
Caemcore builds custom software for companies that outgrew off-the-shelf tools.
Internal systems, integrations, web and mobile apps, and products built from scratch. Small senior team, Warsaw-based, working with clients across the EU, US and UK.
You get a price range and a timeline before development starts, a working demo every Thursday, and a final invoice that matches the number quoted on day one.
Not sure what you need? A 30-minute call, free: what the task actually is, whether it needs building or an off-the-shelf tool covers it, a range and a timeline. You get that whether we work together or not.
FAQ
Can employees keep using Excel for analysis after the migration?
Yes. Provide an export or reporting connection with a clear refresh time, field definitions and appropriate access. Distinguish analysis from operational updates. If a result must change an order, send it through the agreed update process rather than letting an edited export silently become another source of truth.
What if an important formula has no known owner?
Treat its intended result as unresolved. Gather examples, identify who relies on the output and ask that person to approve the business rule before it is reimplemented. Compare old and proposed results on agreed cases. A developer can explain what the formula calculates, but that alone does not establish what the business intended.
Should we import all historical records into the new live database?
Not automatically. Define which history the active workflow needs and which records can remain in a searchable archive. Test archive access before closing an old account or subscription. Preserve required links between current work and historical records, and confirm retention requirements with the responsible team before excluding or deleting anything.
What should happen if departments disagree about the same field?
Assign a business owner to resolve whether the field has one meaning or several legitimate meanings. Record separate definitions where needed instead of forcing different processes into one ambiguous status. Do not choose whichever file is newest as a substitute for that decision. The migration mapping should state the approved interpretation and who accepted it.
Do we need to stop improving the process while the migration is built?
No, but changes need an owner and an impact review. Keep a dated record of approved changes to fields, rules and integrations, and update the migration tests with them. Separate optional redesign from what the first release must preserve. A change to customer identifiers or approval rules may require another trial import before switching.
Sources
- GOV.UK, The Government Data Quality Framework. Checked 12 September 2026.
- Microsoft Support, Keeping leading zeros and large numbers. Checked 12 September 2026.
- PostgreSQL documentation, Constraints. Checked 12 September 2026.
- Google Docs Editors Help, Protect, hide and edit sheets. Checked 12 September 2026.
- OWASP, Authorization Cheat Sheet. Checked 12 September 2026.
The service-company workflow, mapping examples and 120-row trial are hypothetical. They illustrate decisions and acceptance checks, not Caemcore client results or universal migration thresholds.
Manage cookie settings
