Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Error with blank values when reading from an Excel Input step

  1. #1

    Default Error with blank values when reading from an Excel Input step

    Hi,

    I have an excel file in which some fields may take a Data vale or a blank value. When I read the excel file I define the field as "Date" type. I could defne it as "String". However, I need this field to be defined as "Date" type. When I launch the transformation and I always get an error in the row where there is the fist blank value.

    Does anyone know how to deal with blank values when reading data from an Excel Input step.
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    I took your files, fixed the Excel input to use JXL (since you supplied a XLS file rather than XLSX file), and then put the file in the file box.
    I did a preview of your rows, and didn't get an error... But I only got 996 rows.
    So I added another column to your data (in the XLS file) - just a simple row counter.
    I then added the column to the Excel Input and previewed again.
    This time, I get 999 rows (exactly what's in your file), a few of them with <null> in the date column... That's what's to be expected!

    Still no errors.

    If you really want to get help, please provide a fully functional demonstration of the problem. Filenames with ${Internal.Transformation.Filename.Directory} will allow us to simply download the files into a directory and run it, none of the adaptations that I had to do to your prior example.

  3. #3

    Default

    Hi Gutlez,

    I am attaching the files again (I have created a ".rar" file including the xlsx file as I got an error when trying to upload this type of file). Now with the Filename including "${Internal.Transformation.Filename.Directory}" and selecting the "Excel 2007 XLSX (Apache POI Streaming)" Spreadsheet type, I get the error you can see below. However, if I change the Spreadsheet type to "Excel 2007 XLSX (Apache POI)" it works fine.



    ---

    2016/12/23 01:46:01 - input.0 - ERROR (version 6.0.1.0-386, build 1 from 2015-12-03 11.37.25 by buildguy) : Error processing row from Excel file [\Book1.xlsx] : java.lang.NullPointerException
    2016/12/23 01:46:01 - input.0 - ERROR (version 6.0.1.0-386, build 1 from 2015-12-03 11.37.25 by buildguy) : java.lang.NullPointerException
    2016/12/23 01:46:01 - input.0 - at org.pentaho.di.trans.steps.excelinput.ExcelInput.fillRow(ExcelInput.java:135)
    2016/12/23 01:46:01 - input.0 - at org.pentaho.di.trans.steps.excelinput.ExcelInput.getRowFromWorkbooks(ExcelInput.java:613)
    2016/12/23 01:46:01 - input.0 - at org.pentaho.di.trans.steps.excelinput.ExcelInput.processRow(ExcelInput.java:430)
    2016/12/23 01:46:01 - input.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
    2016/12/23 01:46:01 - input.0 - at java.lang.Thread.run(Unknown Source)
    2016/12/23 01:46:01 - input.0 - Finished processing (I=502, O=0, R=0, W=502, U=0, E=1)
    2016/12/23 01:46:01 - example - ERROR (version 6.0.1.0-386, build 1 from 2015-12-03 11.37.25 by buildguy) : Errors detected!
    2016/12/23 01:46:01 - example - ERROR (version 6.0.1.0-386, build 1 from 2015-12-03 11.37.25 by buildguy) : Errors detected!
    2016/12/23 01:46:01 - example - ERROR (version 6.0.1.0-386, build 1 from 2015-12-03 11.37.25 by buildguy) : Errors detected!
    2016/12/23 01:46:01 - example - Transformation detected one or more steps with errors.
    2016/12/23 01:46:01 - example - Transformation is killing the other steps!




    Attached Files Attached Files

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

    Default

    I have no problem reading the supplied file in streaming mode with the date_field changed to type Date (format dd/MM/yyyy) using 6.0.1.0-196 (104.338 total rows, 2.433 missing date values)
    So long, and thanks for all the fish.

  5. #5

    Default

    Hi,

    I made the suggested change in the transformation (set the type to Date with format dd/MM/yyyy) and I still get the same error. If I change the type to String the error is still happening. However, if I change the Spread Sheet Type Engine to "Excel 2007 XLSX (Apache POI)" instead of "Excel 2007 XLSX (Apache POI Streaming)" it works.

    What am I missing here?

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.