Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: problem with Replace null value :- If field value is null Step - Text File Input

  1. #1
    Join Date
    Jun 2010
    Posts
    15

    Post problem with Replace null value :- If field value is null Step - Text File Input

    Hi Experts,

    I have a problem with Replace null value :- If field value is null Step

    My source is a delimetd file( comma seperated ) .And I have some column value as null ( empty string )
    Example:-

    Col_1,Col_2,Col_3
    1,name1,addr1
    2,name2,addr2
    ,name3,addr3
    ,name4,addr4
    3,name5,addr5

    Note:- some of my col_1 value is null.

    And I am loading this file to my target table.
    In between I am using Replace Null value ( for Col_1 --> 0 )
    But this is not working .

    I tried with REPLACE STRING by giving an empty string in quotes ('')
    also i tried with out entering any thing in the search option.

    If i load the entire data into a temp table
    make this as a source ,
    use Replace null step
    then target table step ; It will work.


    Am I missing anything ?

    Thanks in advance

    AMR

  2. #2

    Default

    Hi Amr,

    please make sure you don't use Lazy conversion when reading your files.

    Regards

    Samatar
    Samatar

  3. #3
    Join Date
    Jun 2010
    Posts
    15

    Default

    Hi Samtar,

    Thanks for the response.

    I am new to this tool.

    I am using Text File Input ( not CSV file Input)

    In Text file Input I am not able to find the option " Lazy conversion "
    Do I need to migrate to CSV file input ? or Is there any alternate way ?

    Thanks for your time.

    AMR

  4. #4

    Default

    Ok,

    Can please tell me which version of Kettle you are using and attach a sample transformation.

    Regards

    Samatar
    Samatar

  5. #5

    Default

    try using "select values" and "If field value is null" but you have to mark the field col_1 with "Binary to Normal = Y"

  6. #6
    Join Date
    Jan 2016
    Posts
    7

    Default

    Agree, this seems broken still (pdi-ce-7.0.0.0-25)

  7. #7
    Join Date
    Jan 2016
    Posts
    7

    Default

    WHOA... the issue here is that PDI treats empty strings as NULLs... Unless you add the magical kettle property: KETTLE_EMPTY_STRING_DIFFERS_FROM_NULL=Y

  8. #8
    Join Date
    Jan 2016
    Posts
    7

    Default

    WHOA... PDI treats empty strings the same as NULLs... Unless you set the magical kettle property: KETTLE_EMPTY_STRING_DIFFERS_FROM_NULL=Y

  9. #9

    Default

    THANK YOU! this was bugging me so bad. Fixed my issue of being unable to Insert an empty string into a DB.

  10. #10
    Join Date
    Dec 2013
    Posts
    13

    Default

    For me, select values is working fine. if you convert the col_1 to string and then use "If field value is null" component, then its working fine.
    Thanks & Regards
    Karan Arora

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.