Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: MySQL Bulk Loader with DATATIME Fileds

  1. #1
    Join Date
    Feb 2016
    Posts
    2

    Default MySQL Bulk Loader with DATATIME Fileds

    Extracting data from SF with format of yyyy-MM-dd'T'HH:mm:ss.SSS'Z' which is working. Writing this data to a text file with format yyyy-MM-dd HH:mm:ss which is working. Using this file for bulkloading into MySQL into a column defined as DATETIME. The table values are all zero's. A preview of the data at all the steps shows the correct values. I played with all kinds of formats and column data types to no avail. At one point I was able to get it to load just the date portion with no time but not sure what combination that was. I always spend more time trying to figure out the nuances of datetime/timestamps in Spoon than actually creating the ETL processes. any suggestions would be appreciated.

  2. #2

    Default

    I think you are dealing with a MySQl issue rather thanh a Pentaho issue. The following link describes bulk upload on timestamp fields and the user is getting zeros as you do. The linbk has the solution http://stackoverflow.com/questions/1...mestamp-fields

    Hugo

  3. #3
    Join Date
    Feb 2016
    Posts
    2

    Default

    I did some troubleshooting and the issue is related to the text file output. Defining the filed as timestamp with the format does not put the enclosure character around the date even though the force enclosure around fields is specified. That not enclosed data is then passed to MySQL Bulk import which causes the issue. I validated by changing the field type to string from the SalesForce Fields with the proper format and then it worked. So the issue is Dates and Timestamps do not get an enclosure character in the Text Output. I think this is a bug if you check the box to force the enclosure character.

  4. #4

    Default

    Well I'm glad you got it to work, that what's important after all.

    Hugo

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

    Default

    If you think it's a bug, you should report it using jira:

    http://jira.pentaho.com
    -- Mick --

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.