Data Cleanse
Trim whitespace, remove characters, fix nulls, and standardize text.
What it does
A one-stop tidy-up for messy data. Trim and collapse whitespace, strip characters you don't want (punctuation, digits, letters, HTML tags, invisible characters, accents), turn blanks into nulls or the other way round, and standardize text case, across every column or just the ones you choose. It's the node to put straight after an import from a system you don't control.
Inputs and outputs
| Inputs | One. |
| Outputs | One. |
When to use it
- Clean up imported, scraped or hand-typed text
- Standardize case and spacing before matching or joining
- Strip stray characters before loading into something strict
Examples
Trim whitespace
Leading & trailing whitespace: on
On by default: the single most useful thing this node does.
Strip punctuation
Remove punctuation, except: - /
The exceptions box keeps the characters that carry meaning.
Title-case names
Modify case → Title Case
Turns MARY SMITH and mary smith into Mary Smith.
Blanks to null
Text columns: Blank → null
So an empty string and a genuine null stop being two different things.
Digits only
Remove letters → phone becomes 4155550123
The quickest way to normalize phone numbers before matching.
Accents off
Accents: on → José Muñoz becomes Jose Munoz
So a name typed with and without its accents matches.
Tips
- Trimming leading and trailing whitespace is ON by default; everything else is off until you turn it on.
- Use “Columns to cleanse” to limit the cleanup. Applying case changes or character stripping to every column is rarely what you want.
- The “except” boxes take a list: a multi-character token protects that whole word, a single character protects that character everywhere.
- “Drop empty rows / columns” looks across the WHOLE dataset, so dropping empty columns has to hold the stream while it finds out. A Test run only sees its 50-row sample, so a column that looks empty there may not be.
- This node replaces nulls with a blank or a zero only. For a mean, median or mode, use Fill Missing instead.
- Accents come off Latin, Greek and Cyrillic letters only, and a Polish ł or a Danish ø loses its stroke. A letter that has no accent, like the German sharp s, stays, and so do the marks of other scripts, because in Thai, Hindi or Japanese a mark is part of the word. The same rule is the remove_accents() function, for use in any expression.
- Cleanse before you match. Data Compare and Fuzzy Match both compare what they are given, so trimming afterwards is too late.