Documentation

    Cleanse

    Clean up messy values before you compare or analyze them. Trim text, fix capitals and round decimals. Settings apply across columns, and you can override them for a single column.

    Clean names and amounts

    Manual Input has four rows with name and amount. Cleanse starts with Trim Whitespace, Collapse Whitespace and Title case on, so the padded, unevenly spaced ada lovelace becomes Ada Lovelace.

    The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.

    Result
    nameamount
    Ada Lovelace12.346
    Lin Chen8.764
    5.432
    Sam!
    1. Expand Decimal Columns, select Round Values and set Round Decimals to 2. The amounts become 12.35, 8.76, 5.43 and NULL.
    2. Under Text Columns, select Remove Punctuation. Sam! becomes Sam; the other names stay unchanged.
    3. Expand Null Values and select Remove Null Rows. Only Ada Lovelace and Lin Chen remain, because the other two rows have a missing name or amount.

    When to use

    Use Cleanse before comparing or grouping values, when stray spaces, inconsistent capitals or extra decimals would make equal values look different.

    Read the output

    The output keeps all four rows and both columns. The first name becomes Ada Lovelace, the second becomes Lin Chen, the missing name stays NULL and Sam! keeps its punctuation. The amounts don't change until you turn on Round Values.

    Rounding to two decimal places produces 12.35, 8.76 and 5.43. The missing amount stays NULL. Removing null rows then leaves only the first two complete rows.

    Cleanse changes values in place. It doesn't add separate cleaned columns. The null-row and null-column checks use the input values, so text that becomes an empty string after cleanup doesn't count as NULL.

    To replace missing values rather than remove rows, use Impute.

    To extract text using a specific pattern, use RegEx.

    Inputs and outputs

    Inputs

    1 port
    InRequired

    Accepts: Tables, Geospatial data.

    Accepts one connection on this port.

    Outputs

    1 port
    Out

    Configure the block

    Settings apply to columns of a matching type. A new block changes nothing until you turn on an operation.

    Trim Whitespace

    Remove leading and trailing spaces from text.

    Collapse Whitespace

    Replace runs of whitespace within text with a single space.

    Remove Letters

    Remove ASCII letters A-Z and a-z from text.

    Remove Digits

    Remove digits 0-9 from text. Numeric columns are unaffected.

    Remove Punctuation

    Remove characters outside ASCII letters, digits, underscores and whitespace. This can also remove accented letters and symbols.

    Modify Case

    Choose None, Upper, Lower or Title. Title capitalizes the first character of each space-separated word and lowercases the rest. Default: None.

    Round Values

    Enable rounding for decimal columns. Integer columns remain unchanged.

    Round Decimals

    Choose the number of decimal places when rounding is enabled. Use 0 for whole-number values; this doesn't cast the column to an integer type.

    Truncate To Date

    Convert timestamp columns to DATE, removing the time of day.

    Remove Null Rows

    Drop a row if any affected column contains NULL. The global setting applies to every column unless overridden.

    Remove Null Columns

    Drop affected columns whose input values are all NULL. A column with even one non-NULL value remains.

    Column Overrides

    Expand Per-Column Overrides and then a column to override an individual setting. Unset settings inherit the global options above; an explicit override takes precedence.

    Cleanse FAQs

    Does trimming turn empty text into NULL?
    No. Text with only spaces becomes an empty string. Remove Null Rows checks for NULL, so it keeps a row whose cleaned text is empty.
    Can I clean only one column?
    Yes. Leave the global operation off and turn it on in that column's overrides. You can also turn off a global operation for one column.
    In what order are text operations applied?
    Trim, collapse whitespace, remove letters, remove digits, remove punctuation, then change case. There is no second trim after character removal.
    Does Cleanse parse numbers stored as text?
    No. Rounding only applies to decimal number columns. Use Number first to turn formatted text into numbers.

    Prepare your data. Build it in Strata.

    Strata opens in your browser, with no account.