Hi Achim,
You can achieve this using the
.NET Script Heater
along with the script I have written below.
You have to drag a .NET Script Heater on the
Designer
window for each individual
Excel
field that could possibly contain unprintable characters. Then copy the script below into the Heater configuration and connect the incoming field to the .NET Script Heater as well as its output to the relevant field on the WRITE side.
This script filters the characters of the incoming string. I think it should work for the most frequently used characters.
public object DoWork()
{
object invalue = InValues[0].GetValue();
if (invalue == null)
return invalue;
string replace_with = ""; // = remove from string
// string replace_with = " "; // = replace with space
// add additional characters here
string special_character= "\t\n\r\\[\\]!ß´#\"$§%&' ()*+€,-./:;<=>?@\\^_`{|}~";
Regex myRegex = new Regex(@"[^\w\d" + special_character + "]");
return myRegex.Replace((string)invalue, replace_with);
}
At present it passes through:
- all letters and numbers
- most special characters (see comments in the script)
If some special characters are eliminated that should not be replaced/removed, you just need to add those characters to the list of special characters in the script, between the quote marks (see the comments in the script).
Note: This script makes use of regular expressions (Regex) and hence is only supported by FlowHeater versions 1.2.6 and above. For further information about this, please refer to the
ReleaseNotes
.