Move and clean your data, record by record.

What goes wrong when data moves

• Lost zeros: Excel turns the phone number 01632 960123 into 1632960123, and company numbers lose their leading zero.
• Phone numbers in five formats: ‘020 7946 0001’, ‘(020) 7946-0002’, ‘+44 20 7946 0003’.
• Mixed dates: ‘3/7/21’, ‘2019-03-12’, ‘1 February 2022’ and dates that do not exist, such as 31 April. Is 03/07/2021 in July or March? It depends on who typed it.
• Amounts as text: ‘£1,200.00’, ‘1200 GBP’ or ‘approx. 1200’ instead of a number.
• The same customer twice: ‘St John’s Garden Centre Ltd’ and ‘St. Johns Garden Centre’, with different customer numbers.
• Empty required fields and broken characters from a different file encoding.

The tool: clean your data in the browser

Our cleansing tool is not a mock-up; it really works. It runs entirely in your browser window, with no upload and no server, and you can try it with your own CSV file. The interface is in German and its formatting rules are set for German data (dates as DD.MM.YYYY, five-digit postcodes, +49 phone numbers), but the method is easy to follow. In a project, the rules are set for your data and target system, for example UK postcodes and +44 numbers.

1. 1Read: CSV, TXT or TSV up to 20 MB. Separator and encoding (UTF-8 or Windows-1252) are detected automatically.
2. 2Check columns: the tool guesses each column's type (text, date, amount, email, postcode, phone). You correct it and mark required fields.
3. 3Clean: six rules can be switched on or off, such as trimming spaces. The preview shows new and old values.
4. 4Duplicates: name (ignoring legal form, accents and punctuation), postcode, email and phone are compared. You decide on each pair; the more complete record stays and empty fields are filled from the other.
5. 5Audit log: you download the cleaned CSV and the log, with every change, every merge and every open question.

What the sample file shows

The fictional customer list in the tool has 66 rows. The tool cleans 185 cells, suggests 10 pairs of duplicates and exports 56 rows after merging. Ten values stay open because they cannot be corrected safely, such as a date of 31 April or an email address with ‘(at)’ instead of @. A person resolves those before the data goes into the new system.

How a migration works

1. 1Inventory: which sources, fields and records exist, and in what state? Plus a trial export.
2. 2Mapping and rules: which old column becomes which new field? Formats, required fields and duplicate rules follow the target system, for example a CRM, a membership database or a stock system.
3. 3Trial run: with a complete copy. You get the log and a list of doubtful cases.
4. 4Reconciliation: we count old against new (records, totals, open items) and you check samples.
5. 5Cut-over: final export, import with the same rules as the trial run, sign-off. The cleansing is a repeatable process, not one-off manual work.

Keep the originals: UK record-keeping

Clean the working data you will use in the new system, but keep the original records unchanged. HMRC expects limited companies to keep accounting records for 6 years from the end of the last financial year they relate to, longer in some cases. Self-employed people must keep records for at least 5 years after the 31 January submission deadline of the relevant tax year.

Personal data has its own rules. Under the UK GDPR it must be accurate and, where necessary, kept up to date, and you must not keep it longer than you need it. Which records fall under which rule is for you to check with your accountant or adviser.

Merging customer and supporter lists

Two sites, the accounts list and a sales spreadsheet: in the end each customer should exist once. An exact comparison is not enough, because ‘St John’s’ and ‘St. Johns’ are different names to a computer. We compare several attributes and show for each pair why it may be a duplicate. You decide what is merged, and we record which old customer numbers end up in the new record, so old documents can still be found. Supporter and member lists work the same way.

How the price is set

The effort depends on the number of sources, the volume and condition of the data, the number of rules and doubtful cases, and the target system: does it have an import interface, or must the data arrive in a fixed file format? After the intro call we quote a fixed price for each stage before any code is written. We recommend the inventory and a trial run as the first stage, so you know the state of your data before the main move is commissioned. If the data moves into a new database instead of Excel, the migration is one stage of that project.