Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Extract data from CSV file and then transform it into XML to finally load into target

  1. #1
    Join Date
    Jun 2014
    Posts
    4

    Default Extract data from CSV file and then transform it into XML to finally load into target

    Have a requirement to extract data from CSV file and then transform it into XML to finally load into target.


    The data in the CSV file is

    Number,Email,Email Communication,Member Type,VIN
    85320000399,test@hotmail.com,TRUE,CANLAN,TEST
    81890000887,test@hotmail.com,TRUE,CANLAN,TEST
    85480001175,xx@live.ca,TRUE,CANLAN,TEST
    85370001847,abc@hotmail.com,TRUE,CANLAN,TEST
    85500000418,123ftremblay.ca,TRUE,CANLAN,TEST


    XML format
    <MemberUpdate>
    <MemId>85320000399</MemId>
    <emailAddress>test@gmail.com</emailAddress>
    <optInFlag>true</optInFlag>
    <memberType>CANLAN</memberType>
    </MemberUpdate>POSQTGRESQL table column is of XML datatype
    I created a transformation to do the above but I get an error.


    ERROR: column "content" is of type xml but expression is of type character varying.
    Hint: You will need rewrite or cast the expression. The transformation ran successfully when I changed to the table datatype to "character varying." But I want to do this without making any changes to the database.
    But my question is doesn't "ADD XLM" transformation convert the fields to XLM? If not how do I achieve this?

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

    Default

    There is no XML data type support in Kettle.
    Add XML produces a String and PostgreSQL complains about a non-matching field mapping in a prepared statement.
    You can use one of the Execute SQL Script steps to offer markup as a string to PostgreSQL.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Jun 2014
    Posts
    4

    Default

    Is there any way that I can convert this xml string into actual xml using any transformation?

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

    Default

    How far would it get you, when only simple Java types are supported as field types?
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Jun 2014
    Posts
    4

    Default

    Does that mean there's no way to get this done?

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

    Default

    Wasn't I explicit about this in my first answer?

    You can use Execute SQL Script to load a markup string into a column of type XML.
    So long, and thanks for all the fish.

  7. #7

    Default Done. Follow the attachments carefully to understand. (JOb.kjb)

    1. Copy to result will copy all rows to result. (This is not in loop)2. Job (Is in loop). i) Set Variables will set all rows copied in result as variables. ii) Temp File will create a temporary file on the computer after performing small replace operations. iii) The last step will take the Temp file and filter rows that are unnecessary. Also creates a final file exactly as you wanted.
    Attached Files Attached Files

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.