Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Text File Input to Table Output question

  1. #1
    Join Date
    Jan 2007
    Posts
    11

    Default Text File Input to Table Output question

    I would like to use a Text File Input to Table Output transformation, using a .CSV file as the source and an existing Oracle table as the target. The .CSV file was create by exporting a few records from the source table.

    When ever I set this up, the Table Output step always wants to write ALTER TABLE statements for many of the fields. I cannot seem to get number types to agree.

    I have tried forcing the data types in the Text File Input step to match the Oracle table definition, but even then the Output Table step writes an ALTER TABLE sql statement that adds a new field of the same data type, updates it to the existing contents, drops the original column, then creates a new column matching the original column, updates it, then drops the new column.

    I have also tried using the CSV File Input step, also have exported the data to Excel and tried the Ecxel Input step. All three result in the same issue.

    Is there a way to force the input to match the existing Oracle table structure

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    Table output does not generate alter statements at runtime... You can ask for them in the gui via "get sql" but that's just indicative. As you've seen in the alter statements, this is not always 100% correct.

    Of course your input fields should closely match the database fields of course.

    There's no guarantee that what's in "get sql" is the 100% solution for the table creation, and this is also why they are not automatically executed (no auto create/adapt of tables)

    Regards,
    Sven

  3. #3
    Join Date
    May 2006
    Posts
    4,882

    Default

    What the "get sql" is generate the create table statement if the table does not exist. Or generate the alter statements to make the existing table look like the ideal for your current input.

    For table output, it's assumed that all fields in the input will be put into the table in the order they are in the incoming rows.

    Regards,
    Sven

  4. #4
    Join Date
    Jan 2007
    Posts
    11

    Default

    Thanks, Sven. I'm so much in the habit of executing any SQL generated by the output table set, I guess I forgot that I don't have to. Still failing because of data mismatch errors, I'll have to work thru the whole mess (90 + columns) to make sure that I got it right.

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.