0014 How to strip non-numeric characters automatically

Strip non-numeric characters before loading numeric fields

This example shows how to clean a field that should contain numbers but arrives with currency symbols, commas, spaces, or other text. Databases are rarely impressed by a value like $1,234.00 when the target column expects a number.

Source data example

The source data includes non-numeric characters inside a numeric field. The transformation removes those characters before writing the cleaned value to the target.

Run the example

• Download and install Advanced ETL Processor [Link]
• Download and unzip the example [Link]
• Create a new transformation and open the .ats file.

• Double-click the Reader object and amend the source file path.
• Double-click the Writer object and amend the target file path.
• Run the transformation by pressing the green arrow.

Choose the right transformation function

You can remove unwanted characters with the Delete Characters transformation function, or keep only the allowed characters with the Keep Characters transformation function.

Delete Characters transformation function

Use Delete Characters when you know exactly which characters should be removed, such as currency symbols, commas, and spaces.

Keep Characters transformation function

Use Keep Characters when it is safer to define what is allowed. For numeric fields, that usually means keeping digits and any decimal separator your target format expects.

Performance note

For very large datasets, keep the character lists short. Smaller delete or keep lists are easier for the transformation to process. For small datasets, the difference is usually minor.