Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: java.lang.IllegalStateException: Cannot get a numeric value from a text cell

  1. #1
    Join Date
    Oct 2014
    Posts
    18

    Default java.lang.IllegalStateException: Cannot get a numeric value from a text cell

    Hi All,

    In my excel (2007 xlsx) input , I am using excel input with following criteria
    - Engine = Excel 2007 XLSX (Apache POI)
    - Field - all are string. (all are string because, input cell contains either number & text both)


    On launching KTR, I am receving following error
    2014/12/24 15:31:09 -step 1 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : Error processing row from Excel file [D:\transformation\BSNL\subs_pre\input\bsnl_prepaid_31082014__Prepaid_15.xlsx] : java.lang.IllegalStateException: Cannot get a numeric value from a text cell

    2014/12/24 15:31:09 - step 2 - ERROR (version 5.2.0.0, build 1 from 2014-09-30_19-48-28 by buildguy) : java.lang.IllegalStateException: Cannot get a numeric value from a text cell.

    I am stuck that, this error is coming on only 1 xlsx file out of 20 files.
    Please suggest me solution ASAP.

    Thanks in advance
    Yuvam

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    With the given error message, you should find at least one field in your Excel Input step that has a numeric datatype in spite of the mixed content of the Excel column.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Oct 2014
    Posts
    18

    Default

    Dear marabu

    In my input data, I have around 30 columns and each of them have unstrcutred data.

    For example :
    Columns can contain either all character or all number or mixed. Hence I took the String Data type.

    Now please suggest me the further action.

    Regards,
    Yuvam

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by yuvam View Post
    I took the String Data type.

    This may hold for the Excel Input step (step 1), but what about step 2?

    If you attach a simplified transformation and the one erroneous XLSX-file, I can try to find out if it's you or Kettle doing something wrong.
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Oct 2014
    Posts
    18

    Default

    Hi

    As the data is very critical, I am unable to share with anybody.
    But I can draw out a sample format. check Below

    number1,ID1,DOB,name 1,name2,
    123,jh12,12/22/2012,denis,menis,
    456,jK12,2013/12/21,mark,david,
    <NULL>,jkn1,12/12/2011,mark,henry
    789,ABC,12/11/2011,"Mark /",",,,"

    Above is the sample header and data.

    Step 1: Step 1 is Excel input.
    Step 2: Step 2 is Select/Rename.
    As u can see that column "name 1" have a blank space, therefore I converted it to name1 using select/rename step.
    and later used name1 is Step 3 (JavaScript).


    Important
    I faced the same issue with another excel input, which have same unstructured data.
    On lunching KTR gives same error. Then I changed the format of one column to "#". and it worked.

    But this another excel file, Same concept is not working on same issue.

    Kindly suggest.

    Regards
    Yuvam

  6. #6
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    You said you made Excel Input treat everything as a String for a reason: Missing values, differing number and date formats, etc.
    Then, you use a Select Values step doing a type conversion without guarding against null values in number fields, just to face the same problem you tried to avoid.

    You can guard against missing values during number conversion by using a suitable format string, e.g. # or #.# but you'll have to think of other ways for date and time conversion.
    Basically, you've got two options. You can either indetify a well known set of formats and switch between individual handlers, or you lean to error handling - but only after a careful analysis shows a main format with occasional deviations.
    So long, and thanks for all the fish.

  7. #7
    Join Date
    Oct 2014
    Posts
    18

    Default

    Hi

    Let me test the KTR again with the #.#, will come back with results ASAP.

    thanks

  8. #8
    Join Date
    Oct 2014
    Posts
    18

    Default

    Dear marabu

    I have tested the KTR Excel input with # and #.# both for all the input columns format type but still the problem persist.

    Please suggest me how to add the error handling in Excel input.

  9. #9
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Yuvam,
    I think that marabu is saying that you need to check which rows are numeric and which are not and either split the data flow or use some error handling mechanism.
    Regarding error handling, I would do it BEFORE the Excel step.
    -- Mick --

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.