Every business export is messy in roughly the same ways. Once you recognise the nine, cleaning stops being a mysterious slog and becomes a checklist.
This is the field guide, with the Malaysian specifics that generic advice misses.
What cleaning actually does to a row
1. The header is not on row 1
Exports commonly open with a title, a date range, a company name and a blank row before the real column headers. Every tool that reads the file will treat that first line as the header and produce columns named after a report title.
Fix: find the actual header row and skip everything above it. Check for a footer too — totals rows at the bottom will otherwise become data.
2. Merged cells and multi-row headers
A human-friendly export may span one header across two rows — "Q1" over "Jan / Feb / Mar". Machines read this as mostly-empty cells.
Fix: flatten to one header row, combining levels into single names such as Q1_Jan. Do this before anything else; most later steps depend on clean column names.
3. Numbers stored as text
The most common silent failure. "RM 1,250.00" is text, not a number. Sums return zero or concatenate, sorting puts 9 above 10, and no error appears.
Fix: strip the currency symbol, thousands separators and spaces, then convert. Store money as integer cents where you can — it avoids floating-point rounding surprises in totals.
Watch for negatives in accounting format: (1,250.00) means −1250, and a naive parser will read it as text or drop the sign.
4. Ambiguous dates
The one that causes the most quiet damage in Malaysian files.
3/1/25 is 3 January under DD/MM and 1 March under MM/DD. Many tools default to the US reading. Worse, a file can contain both conventions if data was merged from different sources — and any date where the day exceeds 12 will parse correctly while the rest silently flip.
Fix: state the source convention explicitly, convert everything to ISO 8601 (2025-01-03), then verify against a handful of dates you know. If the file mixes conventions, split by source before parsing.
5. Inconsistent categories
"KL", "Kuala Lumpur", "K.L.", "kuala lumpur" and "KUALA LUMPUR" — five values, one city. Your grouped report shows five rows.
Fix: list the distinct values in every categorical column. This single step exposes most of the problem in seconds. Build a mapping to canonical values, and keep the mapping — you will need it on every future import.
6. Trailing and non-breaking spaces
"Ahmad " and "Ahmad" are different strings, so joins fail and duplicates hide. Non-breaking spaces from copied web content are the invisible version of this.
Fix: trim whitespace and normalise unicode across every text column. Cheap, and it prevents a category of bug that is genuinely painful to diagnose.
7. Duplicate rows that are not identical
The same transaction exported twice with a differently formatted timestamp is not an exact duplicate, so a simple dedupe misses it.
Fix: standardise first (steps 3–6), then deduplicate. Define your business key — what combination of fields makes a row unique — and review a sample of what gets merged before accepting.
8. Mixed identifier formats
NRIC as 880101-14-5501 and 880101145501. Phone as 012-345 6789, 0123456789 and +60123456789. All valid, none matching each other.
Fix: pick a canonical form and convert everything. For phones, E.164 (+60123456789) is the right target. Strip separators from NRIC. Do this before any join or dedupe that uses these fields.
Note the compliance angle: these are personal identifiers, so where the cleaned file lives and who can read it is a PDPA question, not just a technical one.
9. Silent truncation and encoding damage
Long text cut off at a character limit; Malay and Chinese characters rendered as mojibake because the file was saved in the wrong encoding. Both look like ordinary data.
Fix: read and write UTF-8 explicitly. Check the maximum length of text columns — values clustering exactly at 50 or 255 characters indicate truncation upstream, which you cannot fix in the file and must raise with whoever exported it.
The order to run them
- Structure — header row, footer, flatten multi-row headers (1, 2)
- Encoding — UTF-8, check truncation (9)
- Whitespace — trim and normalise (6)
- Types — numbers and dates (3, 4)
- Standardise — categories and identifiers (5, 8)
- Deduplicate — only now (7)
- Review — totals before and after, and the change log
Running deduplication before standardisation is the single most common sequencing mistake, and it silently leaves duplicates in place.
The check that catches everything else
Before and after cleaning, compare three aggregates: row count, total of your main numeric column, and count of distinct customers.
Row count should drop only by the duplicates you intended. The revenue total should not change at all. If it did, a transformation ate records or mangled numbers — and finding out now is much cheaper than finding out from a board pack.
For the principles behind this, see data cleaning with AI and the six quality dimensions.
Our AI Analytics programme works through exactly these problems on real Malaysian business exports — HRD Corp SBL-KHAS claimable for eligible employers.
