Set the clock and make a safe copy (≈5 minutes)
Put a 15‑minute timer on and save a clear backup of the original file before you touch anything — filename with date and a quick note like “backup-before-import”. Keep that backup somewhere different from your working copy (cloud and local) so you can restore quickly if needed.
While the timer is running, note the target system (HubSpot, Xero or another app) and the minimal required fields it needs for an import. Write down those field names beside your spreadsheet so you don’t guess while mapping.
The quick-clean checklist (≈7 minutes)
- Deduplicate: sort by email, then phone, then name+company; merge or flag duplicates. Prefer a single canonical email or contact ID column.
- Canonical contact fields: ensure one email column, one primary phone column (normalise to +44 for UK mobiles where possible), and a stable ID field if you have one.
- Dates and numbers: convert dates to a single format (ISO yyyy-mm-dd avoids ambiguity), and strip currency symbols from numeric amounts (use plain numbers with a dot decimal).
- Addresses: split into consistent columns (street, city, postcode, country). Standardise UK postcodes to uppercase and a single space (e.g. SW1A 1AA).
- Consent & GDPR: include a clear consent column (Yes/No) and consent date if known; never import personal data where you don’t have lawful basis.
- Owners & segments: add an owner email or ID field if your CRM needs one; add a simple segment/tag column for lists or teams.
- Clean text: trim leading/trailing spaces, remove stray commas or formula remnants, and save the file as UTF-8 CSV to avoid encoding issues.
- Mapping notes: add a one-line note in the file or a separate sheet listing how each spreadsheet column maps to the target system’s field names.
Test import, check results and a simple rollback plan (≈3 minutes)
Create a 10‑record test file selecting typical rows (including edge cases: missing phone, non-UK postcode, consent No). Import those ten first into a test area, an isolated pipeline, or a safe list in HubSpot/Xero. Verify the key fields: email, phone, dates, consent, owner assignment and address split — this is where mapping errors show up quickly.
If the test looks good, import the rest. If not, use your backup: delete the test records (or reverse the test import using the system’s bulk-delete by import ID) and fix the mapping. Keep a short import log (date, file name, number of rows, who did it) so you can trace changes later. If you’d like a quick template or someone to run the test import with you, Optira can help as a practical, hands-on option.