The idea
Exports pad values with spaces, tabs and line breaks. A customer code " 005642\t" will not match "005642" in a lookup, so the first job is to trim it. The question is which characters to remove.
Tools disagree about "whitespace". DuckDB's trim(x) removes only spaces. pandas .str.strip() with no argument removes every Unicode whitespace character. A spreadsheet TRIM removes spaces and also squeezes inner runs. If your rules say "ASCII whitespace" you must pass exactly space, tab, CR and LF.
Trimming must never touch the inside of the value or change its type. A code is text: keep leading zeros, and don't convert to a number to "clean" it.
Worked example
Input: customer_code: " 005642\t"
- Visual
Add step → Trim → column customer_code → Characters: ASCII whitespace
- SQL (DuckDB)
trim(customer_code, ' ' || chr(9) || chr(13) || chr(10))
- pandas
df["customer_code"].str.strip(" \t\r\n")
Result: "005642" — the tab is gone, the leading zeros stay.
Common mistakes
- Relying on a tool's default definition of whitespace.
- Converting identifiers to numbers.
- Trimming only one side.
Check yourself
Does DuckDB's trim(x) remove a tab?
No. Without a character list it removes spaces only; pass the characters explicitly.
Why not cast customer_code to an integer to drop the padding?
It destroys leading zeros and turns an identifier into a number.
Practice it
Trim customer_code, status and amount with the character set the rules name.