Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Excel input :Mapping problem

  1. #1
    Join Date
    Nov 2007
    Posts
    14

    Unhappy Excel input :Mapping problem

    hi all

    i want to transfer some of my excel files data to the mysql database table.
    my excel files having several sheet(1,2,3).i want to transfer each sheet into different table in mysql. so i have done some steps with kettle 2.4.0.

    1.Excel input with three sheet

    Sheet1:it has three column
    Sheet2:it has three column
    Sheet3:it has three column

    so i pick excel input into the designer and selected 3 sheet for getting all the record.

    2. now i put another three table output into the designer(Table1,Table2,Table3)

    3.now i select the mapping(generate mapping for the target step)for Table1 and and put the mapping box between excel input and Table output(Table1).i mapped the excel column into the target table column(3 excel column indicate 3 column of target table ).for excel input start row and start column is set to 0(zero) .

    i do the same thing for other two table output

    so here my excel input is one and my target output is three table with mapping.


    when i run the transformation Kettle do not transfer data exactly to the mysql.

    at the output side in mysql i found that target table1 contains one row data from the Shee1,one row data from the Sheet2 and one row data from sheet3. and other two tables are filled with 0(zero) value but Table1 should contain only Sheet1 data and other should have corresponding Sheet data.

    i do the same thing for the text file output,and same result happening.


    this is so so confusing.i just stuck for couple of days.i did search and search in google and also pentaho forum.if you know the reason of problem, plz help me in this regard.

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

    Default

    1) Upgrade at least to kettle 2.5.2

    2) What you want won't work... how do you think PDI will differentiate on which rows to send to which table. The output from PDI for an excel using multiple sheets is just all data after each other. You could then use a filter step to seperate the flow (but you seem to be using select values)

    3) For the mixing of rows you currently see. You probably used distribution mode of the hop instead of copy. Why there are rows in the other table... don't know. Myabe you hit a problem and they rolled back.

    As in the last "duplicate" thread you posted, attach your sample transformation using files as output together with an example xls sheet and we can play a bit with it.

    Regards,
    Sven

  3. #3
    Join Date
    Nov 2007
    Posts
    14

    Default Excel input:Transformation file is here

    hi
    many many thanks for your reply. here is the attachment file of
    xls and kts. please take a look
    thanks once again
    Attached Files Attached Files

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

    Default

    Changed ktr.... you have to put the xls sheet in the same directory as the .ktr.

    Preview each of the dummies, which you can replace by tableoutput.

    Regards,
    Sven
    Attached Files Attached Files

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.