Configuring cleansing actions
Published 16 April 2015
Data Cleanser provides five cleansing actions:
Convert Currency
This action allows you to configure currency conversions.
To convert currencies you must first specify the currencies and exchange rates you want to use. You can do this either by importing a .csv file, or specifying them manually in the Data Cleanser interface.
Importing a .csv file
You can use Microsoft Excel to create a .csv file to import:
- In column A, specify ISO currency codes for the currencies you want to use.
- In column B, specify exchange rate values:
- Save the workbook as a .csv file.
- Import this file using the Data Cleanser interface.
Manually specifying currencies and exchange rates
You must specify a ISO currency code and an exchange rate for each currency you want to use. For example:
Find and Replace
This action searches for, and replaces, strings or regular expressions.
Normalize Letter Case
This action ensures that letters are in the correct uppercase/lowercase format. For example, you may want to ensure that the first letter of every string in a column has an initial capital letter.
You can also search for particular strings or regular expressions that you want to change the case.
Normalize Date
This action allows you to configure how dates are formatted.
Use the Separator character box to determine how the different components of the date are separated. For example, '/' in 29/12/84 or '.' In 29.12.84. This can be an arbitrary string.
Select whether to use a short or long Day format and Month format, for example, 29 June or 29th June for the day; 12 or December for the month.
Use Assume century to specify a century, e.g. 19, so the result would be 12/29/1984.
Trim Strings
This action cleans up strings, removing any unwanted characters or unnecessary white space.