Cleaning Data Before the Move: What Must Not Travel As-Is

Cleaning Data Before the Move: What Must Not Travel As-Is

Part two left you with a one-page list: the critical files, their owners, what depends on them, and whether they are backed up. That list tells you what to move. This part tells you what must not move as it is.

The common assumption is that migration is a technical step: export a file, import it into the new system, done. Technically that is true, and practically it is the fastest way to turn a small problem into an expensive one. A spreadsheet is forgiving by design — it accepts anything you type into any cell and leaves the job of interpreting it to you. A system is not: it expects a date to be a date, a number to be a number, and a customer to be one entity with a record. So when unclean data moves, the mess does not disappear; it surfaces as reports that will not add up, invoices that will not link to their customer, and balances that will not match. Then comes the sentence that ends many projects: the new system does not work.

Why cleaning must come before the move, not after

Delaying it is tempting for a good reason: cleaning is dull work that shows up in no presentation, while a new system screen does. So the call is made to move now and clean later. But once the data is in, it starts moving: invoices are issued against duplicated customers, orders attach to duplicated items, and reports are built on incomplete figures. Every new transaction adds a layer on top of the error that makes correcting it harder and riskier, because editing a record that financial transactions have moved against is nothing like editing a cell in a file.

Unclean data is a recurring cause of stalled system projects in general, and we set it out alongside the other causes in why ERP projects fail and how to make yours succeed. The practical conclusion is that cleaning is at its cheapest while the data sits in a static file, and at its most expensive once it lives in a running system.

The five faults present in almost every sheet

These are not exotic errors; they are the natural result of a tool that permits everything. You will find most of them in your own file:

• One party under several names. The same customer appears three times: once under the trading name, once under the owner's name, and once as an abbreviation typed by someone in a hurry. In the file they are adjacent rows that any reader understands. In a system they become three separate customers, each with its own balance and history, so the relationship record splits and none of the three gives you the full picture. The same fault hits items and suppliers.
• Mixed date formats. A single column holding one date as day/month, another as month/day, and a third written as text such as after the holiday or end of month. As long as humans are the only readers it passes, but any sort, duration calculation or time-based report built on that column will return a wrong answer confidently — which is more dangerous than returning a visible error.
• Numbers stored as text. An amount typed with a currency symbol inside the cell, a quantity with its unit of measure in the same box, or a figure that begins with a stray space. It looks like a number to the eye and is not one to the machine, so it will not sum, compare or migrate — and you usually discover this in the first financial report after the move.
• Columns nobody can define. A column called notes or status or temp, added years ago for a purpose that ended, sometimes filled and sometimes blank. It usually travels because nobody dares delete it, so it enters the system as a field with no definition and no owner, and keeps appearing on screens to confuse whoever comes after you.
• Coloured cells that carry meaning. The most dangerous on the list. Yellow means overdue, red means on hold, green means collected. That is not formatting, it is data — but data stored in the colour and in the memory of whoever applied it. When the file is exported, the text travels and the colour is dropped, so you lose information you were running the business on daily, with no warning issued at all.

The single rule: every meaning outside the cell must become a field

If you keep only one sentence from this part, make it this one: any meaning held in a colour, in a row's position, or in someone's head must become a written column before it travels. The test is simple and unforgiving — export the file to plain text with no formatting at all and ask what information was lost. Everything that disappears in that test is exactly what has to become a field now.

The conversion itself is not technical work; it is an exercise in definition. It is not enough to create a status column — you have to fix its allowed values and settle the ones that overlap. Are on hold and cancelled one state or two? At what exact point does a record become overdue? These are questions about how the company works, not about programming, and they are the most valuable thing an owner takes out of the cleaning phase, because answering them clearly once ends arguments that used to be settled verbally every time.

The Origami view

We are a technology company that builds systems for Saudi businesses, and we treat the cleaning phase as part of designing the system rather than as admin work that precedes it. The reason is that every fault in the data exposes an unwritten business rule: a duplicated customer reveals that no agreed identifier for a party exists, and a coloured cell reveals a real operational state nobody ever formally acknowledged. Those discoveries are the raw material of a correct design, which is why we read them with the client instead of passing them into an import tool.

The order we recommend is to unify definitions first: who counts as one customer, what counts as one item, and which values are allowed in each decision field. After that, migration becomes a clean operation that can be repeated and reviewed. That is where our services in building systems begin, and it is the same logic we read in a real case we covered in from a store to a system: what separates organised operations from improvised ones is not the interface but the data discipline underneath it.

A practical order for cleaning

Cleaning every file completely is a project with no end. Work in an order that gives you something movable within days:

• Start with master data, not transactions. Customers, items and suppliers are short lists used repeatedly, and cleaning them automatically corrects the thousands of rows that will later link to them. Invoices and orders come afterwards.
• Adopt one identifier per entity. A fixed code or number that never changes and is never reused, then map every old name and abbreviation to it. For customers in particular, accurate statutory buyer details are not a nicety, because the e-invoicing requirements of the Zakat, Tax and Customs Authority specify what a tax invoice must carry — and we set out the link between a movement and its invoice in e-invoicing in the factory and warehouse.
• Split every compound cell. Quantity in one column and unit of measure in another, amount in one and currency in another, name in one and mobile number in another. The rule is one box for one piece of information.
• Unify formats before any import. One date format across the whole file, numbers with no symbols inside the cell, and a single format for mobile numbers. The work is repetitive and dull, but it is done once.
• Record your cleaning decisions on a side sheet. What was merged into what, what was deleted, and what remains doubtful. When somebody asks two months later where a particular customer went, that sheet is the answer — and without it the team loses confidence in the whole migration.
• Do not delete the original. Keep the pre-cleaning file as a closed, read-only copy. Cleaning involves judgement, and judgement needs a reference to go back to.

And do not wait for perfect data before moving, because it never arrives. The cleanliness you need is that the critical fields are correct, consistent and defined, and that whatever remains incomplete is known and recorded rather than discovered as a surprise later.

In the coming parts

The data is now fit to move, and what remains is the question that decides whether trading continues through the transition or stops: what moves first? The next part covers migration order, why master data always precedes transactions, and how parallel running works with a declared end date rather than an open one. Then we turn to permissions and who sees what, then to the reports you used to build by hand, and we close the series with the first thirty days after the move.

Sources

• Zakat, Tax and Customs Authority — tax invoice requirements, buyer details and document retention.
• Saudi Data and AI Authority (SDAIA) — the Personal Data Protection Law and rules on handling individuals' data.
• Ministry of Human Resources and Social Development — requirements for keeping employee records and data.
• Ministry of Commerce — provisions on commercial books and records for establishments.
• National Cybersecurity Authority — controls covering data protection and backup.