Data Cleansing Tool
Use Data Cleansing to fix common data quality issues. You can replace null values, remove punctuation, modify capitalization, and more.
Tip
This tool has a One Tool Example. Visit Access Sample Workflows to learn how to access this and many other examples directly in Designer Cloud.
Known Limitations
Numbers with more than 15 digits need to be treated as strings, or they lose precision. Set the column type to a string with the Select tool.
Tool Components
 |
Figure: Data Cleansing tool with anchors.
The Data Cleansing tool has 2 anchors.
Input anchor: Use the input anchor to connect the data you want to cleanse.
Output anchor: The output anchor outputs the cleansed data.
Configure the Tool
Remove Null Data
Use these options to remove entire rows and columns of null data:
Remove Null Rows
Remove all rows with a null value in every column.
Remove rows with null values—doesn't remove rows with empty string values.
Only remove rows that have a null value in every column.
Remove Null Columns
Remove all columns with a null value in every row.
Remove columns with null values—doesn't remove columns with empty string values.
Only remove columns that have a null value in every row.
A message displays in the Results window with the number of columns that were removed.
Select Columns to Cleanse
Check the columns to cleanse. Check Select All to select all columns and uncheck to deselect all columns.
String Data Types
All options, except for Replace Nulls with 0, apply to string data types. Use multiple data cleansing tools in your workflow to specify different options for different columns.
Replace Nulls
Replace with Blanks (String Columns): Replace null values with a blank string value. A blank registers as
" "rather than[Null]. This option is selected by default.Replace with 0 (Numeric Columns): Replace null values with a
0(zero). This option is selected by default.
Remove Unwanted Characters
Leading and Trailing Whitespace: Removes leading and trailing whitespace. This option is selected by default.
Tabs, Line Breaks, and Duplicate Whitespace: Replaces any occurrence of whitespace with a single space, including line endings, tabs, multiple spaces, and other consecutive whitespaces.
All Whitespace: Removes any occurrence of whitespace.
Letters: Removes all letters, including non-Latin alphabet letters like A b Z À é ö.
Numbers: Removes all numbers.
Punctuation: Removes these characters: ! " # $ % & ' ( ) * + , \ - . / : ; < = > ? @ [ / ] ^ _ ` { | } ~
Modify Case
Select Modify Case and then choose an option from the dropdown to change the capitalization of string data types:
Upper Case: Capitalize all letters in a string.
Lower Case: Convert all letters in a string to lowercase.
Title Case: Capitalize the first letter of all words in a string.