Replacing Special Characters
When transforming database data, you might need to remove specific special characters, such as the carriage return/line feed (CR/LF) characters, from the data source. To achieve this, you can use any of the following approaches:
1.Define a node function for the specific database field (or even multiple fields) that you need to process. The node function will receive the value of the database field as input, apply to it some processing, and then return the outcome to the mapping. For more information about this technique, see Defaults and Node Functions.
2.Process database values with the help of MapForce built-in functions. For example, to identify specific characters, including control characters, you can use the char-from-code function from the "core" library. To replace values, use the replace function from the "lang" library.
This topic illustrates the second approach. The database used in this example is the SQLite database but the same strategy can be applied with other database types as well.
Consider an SQLite database consisting of a table "Lines" which has two columns: "ID" and "Description".
The goal is to extract each description to a CSV file (one description per line); therefore, a mapping to achieve this goal could look as follows:
However, because each "Description" row in Access contains multiple lines separated by CR/LF characters, the mapping output includes line breaks also, which is not the intended result:
To overcome this problem, we are going to add to the mapping the char-from-code and replace functions from the MapForce built-in library (see also Add a Function to the Mapping). Every description must be processed so that, whenever the characters above are encountered, they should be replaced by a space character.
In the Unicode chart (http://www.unicode.org/charts/), the LF and CR characters correspond to hex 0A | dec 10 and hex 0D | dec 13 characters, respectively. Therefore, the mapping has to be modified to convert the decimal Unicode values 13 and 10 to a string, so as to allow further processing by the replace function.
If you preview the mapping now, notice that the CR/LF characters within each database field have been replaced by a space.
