Data Cleansing

Parent Previous Next


Data Cleansing Transform


The "Data Cleansing Transform" is used to clean or alter data coming into the transform with rules defined in the UI.



UI Overview


  Name - Select the name of the column to replace

 Action - Select the action to be taken on the data

o  Convert string to proper case (Name, Address, Title, Sentence, User) - This allows you to replace the values of an input column with a properly cased output.

o  Trim Whitespace

 Trim all leading and trailing whitespace characters - Trims all whitespace from the beginning and end of the selected input column data.

 Trim all leading whitespace characters - This option will trim all the whitespace from the beginning of the selected input column data.

 Trim all trailing whitespace characters  - This option will trim all the whitespace from the end of the selected input column data.

o  Trim specified characters

  Trim specified characters from start and end - This option will trim all the specified characters from the beginning and end of the selected input column data.

 Trim specified characters from start - This option will trim all the specified characters from the beginning of the selected input column data.

 Trim specified characters from end - This option will trim all the specified characters from the end of the selected input column data.

o   Change Date Format - Allows the conversion of a date from one format to another.

o   Convert NULL to user defined value

o   Convert blank value to NULL

o   Convert blank value to user defined value

o   Replace alpha/numeric/alphanumeric characters to user defined value

o   Replace specified characters or words with user defined value

o   Replace bad date with user defined value

o   Replace matching regular expression pattern with user defined value

o   Extract data from input string using regular expression

o   Replace invalid characters that cannot be part of an XML Document

o   Replace non-printable characters


Defining a Data Cleansing Rule

1.   Choose a column to replace from the "Name" column of the editor window.



2.   Next, select an action (rule) from the "Action" column of the editor window. This will be the action taken on the data coming in.



3.   Setup the parameters for the selected action. Each parameter has a help tip that shows up at the bottom of the drop down menu. You can click the ellipsis at the top right of the help-tip to open the full info window for that parameter.



4.   Each parameter has a type associated with it. This allows you to choose how the transform should fill that parameter value during runtime execution.



5.   Once configured, click "OK" to save the cleansing rule.


Please see the Error Row Handling page for more information about this functionality.