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. This can be done with the help of the MapForce library function char-from-code.
Consider a Microsoft Access 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 Working with Functions). 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.
© 2019 Altova GmbH