Kettle using JavaScript steps to filter special characters

Kettle using JavaScript steps to filter special characters

Using kettle when extracting large amounts of Excel data, always encountering some special characters in Excel, causing the ExecuteSQL script step to fail, this article records some methods to filter out some special characters. Peers have a better way to achieve, welcome guidance, learn together.

The complete implementation transformation is as follows

1. Use JavaScript steps to filter the characters of all string fields

The JavaScript steps can be implemented in code to avoid adding too many steps and filtering all the fields at once; The code is as follows:

for (vari=0;i<Getinputrowmeta(). Size (); i++) {/

var valuemeta=Getinputrowmeta(). Getvaluemeta (i);

if (Valuemeta.gettypedesc (). Equals ("String")) {

var fstr= Row[i];

fstr=Replace(Row[i],' \ n ',');

fstr=Replace(fstr,' \ R ',');

fstr=Replace(fstr,' \ t ',');

fstr=Replace(fstr,'; ',';');

fstr=Replace(fstr,' •',');

fstr=Replace(fstr,"'","" ");

ROW[I]=FSTR;

}

}

2. Use regular expressions to filter repeating characters

The actual project encountered in the Excel file has several-number, the number is indeterminate, the above way is not, the Replace in string step can be resolved. Useregex is a switch that determines whether a regular expression is used, the search fills in an expression, and replacewith uses a string to replace it.

3. Intercept length

If the content of the data source exceeds the length of the database field, insert an error, using the string cut step can be implemented;

Kettle using JavaScript steps to filter special characters