Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: pdi-cd-8.1.0.0-365 / 8.2.x.x Excel Writer Step DATE TIMESTAMP Column null in Sheet

  1. #1
    Join Date
    Sep 2006
    Posts
    17

    Default pdi-cd-8.1.0.0-365 / 8.2.x.x Excel Writer Step DATE TIMESTAMP Column null in Sheet

    Hi All,

    Test Environment

    Windows 8.1/10 64 bit
    Oracle 12.2.0.1
    java version "1.8.0_131"
    pdi-cd-(7.1,8.1,8.2)

    What I did

    Create simple transformation
    Table Input step (with select * from user_objects)
    Excel Writer Step (xlsx) writes file to local folder

    What I found

    The Excel file has been written but no content (null) in the DATA/TIMESTAMP Cells. This means the oracle table contains two DATE fields CREATED and LAST_DDL_TIME. This fields where empty in the excel file.

    Further I tried to set in the Excel Writer Step a format for the date fields --> nothing changed --> column are null
    Further I set the values in the select statement of table input step to a formated string for the DATE fields, means: to_char(DATE,'dd.mm.yyyy') --> now the fields are filled in the excel file. If I use the "format cells" use case in the file it sais "User defined format", but it is not a DATE.

    Question/s

    in previous versions (e.g. pdi-4.4 )we did not need formatting. Do I always have to specify formatting in the table input step?
    A statement like "select * from my_table" with TIMESTAMP or DATE columns would be null for the columns?
    Do I use the Excel Writer Step in a wrong manner?

    Thank you for helping me

    T.

  2. #2
    Join Date
    May 2016
    Posts
    280

    Default

    I have tried with PDI 6.1, Java 1.8.0_191, Oracle DB 12.2.0.1.0, JDBC Driver: ojdbc6.jar, and I can reproduce your problem. If you don't want to transform the TIMESTAMP columns to VARCHAR, you can use the step Select values, and use the Meta-data tab to change all the Timestamp columns to Date type columns (you only need to specify the columns you want to change) that way the Write to excel step works.
    Maybe with a newer ojdbc library it might work directly, I use ojdbc6 because it was the one published at the time to work with Oracle 11.2 databases.
    Regards
    OS: Ubuntu 16.04 64 bits
    Java: Openjdk 1.8.0_131
    Pentaho 6.1 CE

Tags for this Thread

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.