Module 24 · Advanced · 9 min read
Cleansing and mapping the data
You will be able to: Profile and clean source data before import; Build a mapping workbook from source fields to Zeyto fields; Apply Zeyto's data rules when you prepare files.
- Read not done yet
- Self-check: not taken not done yet
- Lesson complete not done yet
Data you load without cleaning becomes the client's problem for years. Cleansing is done by the client's data owners with your guidance; mapping is yours.
Cleansing steps
- Profile: count rows, blanks, duplicates and inactive records in each file.
- Standardise: names, phone formats, countries, units of measure, codes.
- De-duplicate: the same customer entered twice under different names, TRNs or phone numbers.
- Validate: TRNs, tax codes, units, credit terms, negative stock.
- Decide: records without activity in the agreed period stay in the archive.
- Freeze: once a file is signed off by its owner, changes go through you.
4,200 customers, 1,990 to loadA distributor's customer file has 4,200 rows. Profiling finds 310 duplicates and 1,900 inactive accounts with no balance and no activity for 24 months. Load count: 4,200 - 310 = 3,890; 3,890 - 1,900 = 1,990 customers. The 2,210 excluded rows are saved in the archive file with the reason for each.
The mapping workbook
| Source field | Zeyto field | Transformation | Check | Owner |
|---|---|---|---|---|
| Customer name | Name | Trim spaces, single spelling | No duplicates in file | Sales manager |
| VAT number | TRN | Digits only; empty if none | Exactly 15 digits | Accountant |
| Credit days | Credit Days | Number | Matches payment term | Sales manager |
| Tax percent 5/0/exempt | Tax code SR5, ZR, EX, OOS or RCM | Map by rule | Every item has a code | Accountant |
| Unit 'Pcs', 'PC', 'Piece' | Unit | Map to one Zeyto unit | Unit exists before items | Storekeeper |
| Ledger balance | Debit or Credit amount | Sign rule agreed in writing | Trial balance totals match | Accountant |
Zeyto rules that affect your files
- The Code column is the record's key. Do not change a code after it is loaded.
- One TRN per business: duplicates of a TRN across customers must be resolved.
- Amounts take 2 decimals, quantities 4, unit prices and costs 4, rates 6. A number with more decimals is rejected, never rounded for you.
- Dates are written YYYY-MM-DD; day-first dates and Excel date cells are also accepted.
- A group, unit, parent, tax code, account, party, item or warehouse that does not exist is rejected by name, so load the list it points at first.
- CSV files in UTF-8 keep Arabic text correct. The old .xls format is not supported; save as .xlsx.
TipRun the profile again after cleansing and send the client a before-and-after count. It proves the work and builds trust.
Practise in the demo
- Create a mapping workbook with six rows for a customer file and add a check for each.
- Open Sales, then Customers in the demo and note which fields a customer record has, to compare with a source file.
- Open https://demo.zeyto.app/demo-app/ and sign in with the demo login. This is a public demo with sample data, so nothing you do there affects a real business.
Public demo company, sign in as admin / demoAdmin123. Data refreshes nightly.
Key takeaways
- Cleanse by profiling, standardising, de-duplicating, validating and deciding.
- Map every source field to a Zeyto field with a rule, a check and an owner.
- Zeyto rejects over-precise numbers rather than rounding them, and needs referenced lists loaded first.
Check yourself
3 questions on this lesson. Not timed, not the exam, and you can try again. Score 60% or more, and read the lesson, to complete it.