topic Re: Int column having Null values in Talend Studio

Int column having Null values

Anonymous — Tue, 24 Jul 2018 19:35:11 GMT

Hi,

I have a problem with Null values present in my Integer column.

FLow :

Input(.txt )--->Tmap--->Snowflake ouput

Tmap : using the variable

row5.Column28==null||row5.Column28.equals("")?null:new Integer(row5.Column28)

But giving me an error :

NumberFormatException: For input string: "NULL".

Could you please let me know what am I doing wrong ?

TIA

Re: Int column having Null values

Anonymous — Tue, 24 Jul 2018 21:36:14 GMT

row5.Column28 is a String of the value "NULL". This would meet your criteria of it not being null and it not being equal to an empty String. You need to check if your String is numeric. There is no Java function for this, but you can create one. Here is an example you could use (set it up in a routine)....

public static boolean isNumeric(String strNum) {
    boolean ret = true;
    try {

        Integer.parseInt(strNum);

    }catch (NumberFormatException e) {
        ret = false;
    }
    return ret;
}

Assuming this were to be added to a routine called MyUtils, your code would change to....

routines.MyUtils.isNumeric(row5.Column28) ? new Integer(row5.Column28) : null

Re: Int column having Null values

aashish_21nov — Wed, 25 Jul 2018 10:30:43 GMT

Hi @nbhandari,

you can do so without any help of routine as well.

while reading input.txt make column28 datatype change to String type. And in tmap write below code:

row5.Column28.equalsIgnoreCase("NULL") ? null : Integer.parseInt(row5.Column28)

Re: Int column having Null values

Anonymous — Wed, 25 Jul 2018 11:30:37 GMT

@aashish wrote:

Hi @nbhandari,

you can do so with any help of routine as well.

while reading input.txt make column28 datatype change to String type. And in tmap write below code:

row5.Column28.equalsIgnoreCase("NULL") ? null : Integer.parseInt(row5.Column28)

Your code will fall over if Column28 is null or if it is an empty String or if it is any String that is not "NULL" or not a numeric String. The reason I gave the answer I did is because it covers all bases.

Re: Int column having Null values

aashish_21nov — Wed, 25 Jul 2018 12:27:44 GMT

dear @rhall we can check all this thing in tmap as well:

Relational.ISNULL(column_name) || column_name.isEmpty() || column_name.equalsIgnoreCase("NULL") : null : column_name

Re: Int column having Null values

Anonymous — Wed, 25 Jul 2018 13:18:33 GMT

Integer.parseInt(row5.Column28) will return a NumberFormatException which will kill the job if the String passed to it is not a number. There was a reason I supplied the answer that I gave.

Re: Int column having Null values

aashish_21nov — Thu, 26 Jul 2018 07:03:57 GMT

in that case, your code also returns incorrect data like if we pass decimal place double/float value will get null only.

Note: if we pass 1.9 then it returns numberformatexception and our routine will pass null to the output which is also not correct as per my understanding.

Re: Int column having Null values

Anonymous — Thu, 26 Jul 2018 09:16:32 GMT

Look at the original code. They want an Integer. They are parsing an Integer. As such, the code is fine. I can only work to the requirements I am given. It is correct that it will return false for a decimal. However, if they want to accept Integers and Doubles (the original code will have to change parse an Integer or a Double), they can switch the method around to use Double.parseDouble().