Configuring cleansing actions

Data Cleanser provides five cleansing actions:

• Convert Currency
• Find and Replace
• Normalize Letter Case
• Normalize Date
• Trim Strings

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:

1. In column A, specify ISO currency codes for the currencies you want to use.
2. In column B, specify exchange rate values:

   

3. Save the workbook as a .csv file.
4. 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.