Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Can't populate data to table from Excel Sheet

  1. #1
    Join Date
    Feb 2013
    Posts
    530

    Default Can't populate data to table from Excel Sheet

    Hi Forum,

    I'm trying to populate an excel sheet of data to a table component. I can preview the data on "Excel input step" & I'm hoping it to "Table output" step and I can get DDL but when I execute it the table is not created and showing me below error.


    Sample data in Excel preview & the metadata is of String Type for all fields. From the log I understood that when the filed reaches 1960 something is happening & I'm unable to find out it why ?

    Country Name Country Code Indicator Name Indicator Code 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014
    Aruba ABW Literacy rate, adult total (% of people ages 15 and above) SE.ADT.LITR.ZS 97.2912521 96.8226395
    Andorra AND Literacy rate, adult total (% of people ages 15 and above) SE.ADT.LITR.ZS
    Afghanistan AFG Literacy rate, adult total (% of people ages 15 and above) SE.ADT.LITR.ZS 18.1576805 31.7411175


    Error :

    ERROR: syntax error at or near "1960"
    Position: 148

    at org.pentaho.di.core.database.Database.execStatement(Database.java:1485)
    at org.pentaho.di.core.database.Database.execStatement(Database.java:1433)
    at org.pentaho.di.ui.core.database.dialog.SQLEditor.exec(SQLEditor.java:398)
    at org.pentaho.di.ui.core.database.dialog.SQLEditor.access$200(SQLEditor.java:81)
    at org.pentaho.di.ui.core.database.dialog.SQLEditor$7.handleEvent(SQLEditor.java:242)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.pentaho.di.ui.trans.steps.tableoutput.TableOutputDialog.open(TableOutputDialog.java:884)
    at org.pentaho.di.ui.spoon.delegates.SpoonStepsDelegate.editStep(SpoonStepsDelegate.java:124)
    at org.pentaho.di.ui.spoon.Spoon.editStep(Spoon.java:8797)
    at org.pentaho.di.ui.spoon.trans.TransGraph.editStep(TransGraph.java:3027)
    at org.pentaho.di.ui.spoon.trans.TransGraph.mouseDoubleClick(TransGraph.java:744)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)
    at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)
    at org.pentaho.di.ui.spoon.Spoon.readAndDispatch(Spoon.java:1316)
    at org.pentaho.di.ui.spoon.Spoon.waitForDispose(Spoon.java:7979)
    at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:9310)
    at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:654)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.pentaho.commons.launcher.Launcher.main(Launcher.java:92)
    Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "1960"
    Position: 148
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:405)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:397)
    at org.pentaho.di.core.database.Database.execStatement(Database.java:1459)
    ... 27 more
    - Sadakar Pochampalli

  2. #2
    Join Date
    Nov 2013
    Posts
    382

    Default

    Just a guess ... your row fields are named according to the header row, so there is a field named 1960, another named 1961 and so forth. Are you trying to create a postgress table with a field named 1960? Unsure but I would say this is not a valid name.

  3. #3
    Join Date
    Feb 2013
    Posts
    530

    Default

    Hi DepButi,

    I just wanted to dump the Excel data to postgresql table for staging purpose. while generating the DDL it came like 1960,1961 .. and etc .. Next I enclosed them in double quotes and I can have table in postgresql database.

    I thought I am done but again I'm struck with below error while executing the transformation. (I also tried by unchecking batch update and left with same error).

    2015/04/30 16:06:41 - Table output.0 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Unexpected batch update error committing the database connection.
    2015/04/30 16:06:41 - Table output.0 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseBatchException:
    2015/04/30 16:06:41 - Table output.0 - Error updating batch
    2015/04/30 16:06:41 - Table output.0 - Batch entry 0 INSERT INTO "public".test ("Country Name", "Country Code", "Indicator Name", "Indicator Code", 1960, 1961, 1962, 1963, 1964, 1965, 1966, 1967, 1968, 1969, 1970, 1971, 1972, 1973, 1974, 1975, 1976, 1977, 1978, 1979, 1980, 1981, 1982, 1983, 1984, 1985, 1986, 1987, 1988, 1989, 1990, 1991, 1992, 1993, 1994, 1995, 1996, 1997, 1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014) VALUES ( 'Aruba', 'ABW', 'Literacy rate, adult total (% of people ages 15 and above)', 'SE.ADT.LITR.ZS', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ' 97.291252136', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ' 96.822639465', NULL, NULL, NULL, NULL) was aborted. Call getNextException to see the cause.
    2015/04/30 16:06:41 - Table output.0 -
    2015/04/30 16:06:41 - Table output.0 - at org.pentaho.di.core.database.Database.createKettleDatabaseBatchException(Database.java:1351)
    2015/04/30 16:06:41 - Table output.0 - at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1340)
    2015/04/30 16:06:41 - Table output.0 - at org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:571)
    2015/04/30 16:06:41 - Table output.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:96)
    2015/04/30 16:06:41 - Table output.0 - at java.lang.Thread.run(Thread.java:745)
    2015/04/30 16:06:41 - Table output.0 - Caused by: java.sql.BatchUpdateException: Batch entry 0 INSERT INTO "public".test ("Country Name", "Country Code", "Indicator Name", "Indicator Code", 1960, 1961, 1962, 1963, 1964, 1965, 1966, 1967, 1968, 1969, 1970, 1971, 1972, 1973, 1974, 1975, 1976, 1977, 1978, 1979, 1980, 1981, 1982, 1983, 1984, 1985, 1986, 1987, 1988, 1989, 1990, 1991, 1992, 1993, 1994, 1995, 1996, 1997, 1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014) VALUES ( 'Aruba', 'ABW', 'Literacy rate, adult total (% of people ages 15 and above)', 'SE.ADT.LITR.ZS', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ' 97.291252136', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ' 96.822639465', NULL, NULL, NULL, NULL) was aborted. Call getNextException to see the cause.
    2015/04/30 16:06:41 - Table output.0 - at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2743)
    2015/04/30 16:06:41 - Table output.0 - at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1928)
    2015/04/30 16:06:41 - Table output.0 - at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:405)
    2015/04/30 16:06:41 - Table output.0 - at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2892)
    2015/04/30 16:06:41 - Table output.0 - at org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1327)
    2015/04/30 16:06:41 - Table output.0 - ... 3 more
    2015/04/30 16:06:41 - Table output.0 - Finished processing (I=0, O=0, R=248, W=0, U=0, E=1)
    2015/04/30 16:06:41 - test2 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Errors detected!
    2015/04/30 16:06:41 - Spoon - The transformation has finished!!
    2015/04/30 16:06:41 - test2 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Errors detected!
    2015/04/30 16:06:41 - test2 - ERROR (version 5.3.0.0-213, build 1 from 2015-02-02_12-17-08 by buildguy) : Errors detected
    - Sadakar Pochampalli

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

    Default

    Invalid names are still not quoted.
    Try with advanced driver option "Quote all in database" enabled.
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Nov 2013
    Posts
    382

    Default

    Even if marabu suggestion is right, IMHO having such names as column names will be a constant source of problems.

    If this is a table you are creating from scratch and you have control over it ... use non-numeric names. As simple as changing the field name in the Excel Input step on the Fields tab. Edit each field name from 1960 to f1960 (or whatever non numeric name suits you) and so on.

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

    Default

    Quote Originally Posted by DepButi View Post
    IMHO having such names as column names will be a constant source of problems.
    I couldn't agree more
    So long, and thanks for all the fish.

  7. #7
    Join Date
    Feb 2013
    Posts
    530

    Default

    Thank you both of you for your valuable suggestions & time.

    @ DepButi : I am developing table from scratch & I can have control over it but the problem is I have 50+ fields as metadata. I want it from ETL generation instead manually editing the meta data.

    @ Marabu : I can not find the option "Quote all in database" in Table component output step. Could you please tell me where can I do that change ?

    Thank you for your time.
    - Sadakar Pochampalli

  8. #8
    Join Date
    Nov 2013
    Posts
    382

    Default

    Database connection => Advanced => Quote all in database

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.