Cleaning Data with tReplace in Talend Open Studio

If you need to clean data -- like mapping a source system-specific value to a standardized one -- use the tReplace component in Talend Open Studio.

The tReplace component is used to perform a string substitution on a column.  Take the following spreadsheet with a column 'SYSTEM_FUND_SOURCE_NM'.

Excel Spreadsheet with Source Name Column

The source system-specific value 'Frederick County Government' will be mapped to a standard value 'Local Government'.

Start with the following job that contains a tExcelInput, tReplace, and tLogRow.

A Talend Job with a tReplace

To configure the tReplace, first edit the schema so that the input is routed directly to the output.  Select the component and press the Edit Schema button.

Schema Mapping Input to Output

Simple Replace

The Simple Replace will take a string and swap in a replacement value.

Basic Replace in Talend Open Studio

This will swap "Frederick County Government" for "Local Government" for all records with the value in the SYSTEM_FUND_SOURCE_NM column.

Advanced Replace

The Simple Replace is fine for hardcoded strings as in the previous example.  However, the strings may need to be generalized to handle additional cases.  Instead of simply replacing 'Frederick County Government' with 'Local Government', the Advanced Replace will allow a '*County Government' to match the string.

Advanced Configuration of Talend tReplace

The result of running both configuration is the following from tLogRow.

Result of Running a Replacement

tReplace is used to swap out one value for another based on text patterns.  The Simple Replace works for literal strings.  There's supposedly a shorthand glob feature ('*' wildcard), but that doesn't seem to be working on my version of Open Studio.  For more flexibility used the Advanced option.