Thursday, October 1, 2009

Converting binary data type to string in...

Converting binary data type to String in Kettle

Sometimes you might come across data that is store in in binary form in a table. To convert this data you chose one of the following approaches:

Convert directly using SQL in the database input step

One quick method would be to use the CONVERT or CAST function (test which one works best for you):

CONVERT(prodCode USING latin1) AS prodCode

CAST(prodCode AS SIGNED) AS prodCode

Select Values ... step

Go to the Meta-data tab, choose your binary field and set Binary to Normal to Y.
Note: You can only use one tab at a time in the Select Values ... step!

Modified Java Script Value Step

If you have to import the binary data into Kettle, you can use this approach:

var string = new, "UTF-8");


  1. Some things are not clear to me:
    1. where can I use the syntax?
    Is it in a User Defined Java Class?
    And is yourField in that case a FieldHelper?
    2. Where can I use the vfget(..) syntax? In what step?

    What I want to do is lookup a String that contains linefeeds. If I lookup as String type I loose the linefeeds. If I lookup like Binary, I face the problem you describe in this article.

    But I do not understand how to use your hints.

  2. var string = new, "UTF-8");

    -> applies to the Modified Java Script Value step.

    For SQL, you can ignore vfget completely, it is just a user defined function.