Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Excel Writer - Strange behaviour

  1. #1

    Exclamation Excel Writer - Strange behaviour

    Hi All,
    I have an issue with the step Microsoft Excel writer. Ii'm using this step to write the data from a SQL query. The thing is that the query has fields that are dates (type date in the database) but when I send it to the excel writer step it writes numbers, so it doesn't recognize that the field is a date, even when in the list of fields inside the excel writer step that field is a date.
    Could you please help me ? I was searching in the forums but I didn't find anything related exactly to this. Also, find attached a couple of screenshoots that could help to a better undertanding of the issue.
    I'm using a template but for generate the first sheet, not the one with the dates fields. For that one I'm not using any template.
    Thanks!!!
    Attached Images Attached Images    

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

    Default

    I suggest you specify yyyy-mm-dd as the format for your date columns in the MS Excel Writer step.
    So long, and thanks for all the fish.

  3. #3

    Default

    when I did that I got the following error:

    2012-06-19 20:02:52,211 ERROR [org.pentaho.di] Microsoft Excel Writer - Unexpected error
    2012-06-19 20:02:52,211 ERROR [org.pentaho.di] Microsoft Excel Writer - java.lang.NoClassDefFoundError: org/apache/poi/ss/util/CellUtil
    at org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.writeField(ExcelWriterStep.java:394)
    at org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.writeNextLine(ExcelWriterStep.java:288)
    at org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.processRow(ExcelWriterStep.java:149)
    at org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
    at java.lang.Thread.run(Thread.java:662)
    Caused by: java.lang.ClassNotFoundException: org.apache.poi.ss.util.CellUtil
    at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1645)
    at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1491)
    ... 5 more


    2012-06-19 20:02:52,213 ERROR [org.pentaho.di] SettlementReport7H - Errors detected!
    2012-06-19 20:02:52,213 ERROR [org.pentaho.di] SettlementReport7H - Errors detected!
    2012-06-19 20:02:52,883 INFO [org.pentaho.di] Microsoft Excel Writer - Finished processing (I=0, O=1, R=1, W=0, U=0, E=1)
    2012-06-19 20:02:52,884 INFO [org.pentaho.di] SettlementReport7H - SettlementReport7H
    2012-06-19 20:02:52,885 INFO [org.pentaho.di] SettlementReport7H - SettlementReport7H
    2012-06-19 20:02:53,460 INFO [org.pentaho.di] Segments by TA - Finished reading query, closing connection.
    2012-06-19 20:02:53,993 ERROR [org.pentaho.di] SettlementReport7H - Something went wrong while trying to stop the transformation: java.lang.NullPointerException
    2012-06-19 20:02:53,994 ERROR [org.pentaho.di] SettlementReport7H - java.lang.NullPointerException
    at com.mysql.jdbc.StatementImpl.cancel(StatementImpl.java:421)
    at org.apache.commons.dbcp.DelegatingStatement.cancel(DelegatingStatement.java:249)
    at org.pentaho.di.core.database.Database.cancelStatement(Database.java:633)
    at org.pentaho.di.core.database.Database.cancelQuery(Database.java:618)
    at org.pentaho.di.trans.steps.tableinput.TableInput.stopRunning(TableInput.java:293)
    at org.pentaho.di.trans.Trans.stopAll(Trans.java:1352)
    at org.pentaho.di.job.entries.trans.JobEntryTrans.execute(JobEntryTrans.java:1036)
    at org.pentaho.di.job.Job.execute(Job.java:503)
    at org.pentaho.di.job.Job.execute(Job.java:642)
    at org.pentaho.di.job.Job.execute(Job.java:642)
    at org.pentaho.di.job.Job.execute(Job.java:368)
    at org.pentaho.di.job.Job.run(Job.java:288)


    2012-06-19 20:02:54,189 INFO [org.pentaho.di] Segments by TA - Finished processing (I=5, O=0, R=1, W=3, U=0, E=0)
    2012-06-19 20:02:54,193 INFO [org.pentaho.di] SettlementReport7H - Finished job entry [SettlementReport7H] (result=[false])
    2012-06-19 20:02:54,271 INFO [org.pentaho.di] SettlementReport7H - Finished job entry [SummaryPage_SR_7HNew] (result=[false])
    2012-06-19 20:02:54,271 INFO [org.pentaho.di] SettlementReport7H - Job execution finished
    2012-06-19 20:02:54,272 ERROR [org.pentaho.platform.plugin.action.kettle.KettleComponent] Error Start: Pentaho Pentaho Platform Engine Core 3.10.0-stable.48193
    2012-06-19 20:02:54,272 ERROR [org.pentaho.platform.plugin.action.kettle.KettleComponent] b6f4f085-ba49-11e1-a29f-8d45f6b2a5e7:COMPONENT:context-544610226-1340136156271:7HSettlementReport.xactionKettle.ERROR_0008 - [es_383] An unexpected error occurred running the transformation org.pentaho.platform.plugin.action.kettle.KettleComponentException: Kettle.ERROR_0014 - [es_249] Error occurred during transformation execution
    org.pentaho.platform.plugin.action.kettle.KettleComponentException: Kettle.ERROR_0014 - [es_249] Error occurred during transformation execution
    at org.pentaho.platform.plugin.action.kettle.KettleComponent.executeJob(KettleComponent.java:875)
    at org.pentaho.platform.plugin.action.kettle.KettleComponent.executeAction(KettleComponent.java:556)
    at org.pentaho.platform.engine.services.solution.ComponentBase.execute(ComponentBase.java:463)
    at org.pentaho.platform.engine.services.runtime.RuntimeContext.executeComponent(RuntimeContext.java:1293)
    at org.pentaho.platform.engine.services.runtime.RuntimeContext.executeAction(RuntimeContext.java:1262)
    at org.pentaho.platform.engine.services.runtime.RuntimeContext.performActions(RuntimeContext.java:1161)
    at org.pentaho.platform.engine.services.runtime.RuntimeContext.executeLoop(RuntimeContext.java:1105)
    at org.pentaho.platform.engine.services.runtime.RuntimeContext.executeSequence(RuntimeContext.java:987)
    at org.pentaho.platform.engine.services.runtime.RuntimeContext.executeSequence(RuntimeContext.java:897)
    at org.pentaho.platform.engine.services.solution.SolutionEngine.executeInternal(SolutionEngine.java:399)
    at org.pentaho.platform.engine.services.solution.SolutionEngine.execute(SolutionEngine.java:317)
    at org.pentaho.platform.engine.services.solution.SolutionEngine.execute(SolutionEngine.java:193)
    at org.pentaho.platform.engine.services.BaseRequestHandler.handleActionRequest(BaseRequestHandler.java:159)
    at org.pentaho.platform.web.servlet.ViewAction.handleActionRequest(ViewAction.java:167)
    at org.pentaho.platform.web.servlet.ViewAction.doGet(ViewAction.java:316)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.pentaho.platform.web.http.filters.PentahoWebContextFilter.doFilter(PentahoWebContextFilter.java:92)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.pentaho.platform.web.http.filters.PentahoRequestContextFilter.doFilter(PentahoRequestContextFilter.java:84)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:378)
    at org.springframework.security.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)
    at org.springframework.security.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
    at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
    at org.springframework.security.ui.ExceptionTranslationFilter.doFilterHttp(ExceptionTranslationFilter.java:101)
    at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)
    at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
    at org.pentaho.platform.web.http.security.SecurityStartupFilter.doFilter(SecurityStartupFilter.java:103)
    at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
    at org.springframework.security.providers.anonymous.AnonymousProcessingFilter.doFilterHttp(AnonymousProcessingFilter.java:105)
    at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)
    at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
    at org.pentaho.platform.web.http.security.RequestParameterAuthenticationFilter.doFilter(RequestParameterAuthenticationFilter.java:169)
    at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
    at org.springframework.security.ui.basicauth.BasicProcessingFilter.doFilterHttp(BasicProcessingFilter.java:174)
    at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)
    at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
    at org.springframework.security.ui.AbstractProcessingFilter.doFilterHttp(AbstractProcessingFilter.java:278)
    at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)
    at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
    at org.springframework.security.ui.logout.LogoutFilter.doFilterHttp(LogoutFilter.java:89)
    at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)
    at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
    at org.pentaho.platform.web.http.security.HttpSessionReuseDetectionFilter.doFilter(HttpSessionReuseDetectionFilter.java:134)
    at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
    at org.springframework.security.context.HttpSessionContextIntegrationFilter.doFilterHttp(HttpSessionContextIntegrationFilter.java:235)
    at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)
    at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
    at org.springframework.security.wrapper.SecurityContextHolderAwareRequestFilter.doFilterHttp(SecurityContextHolderAwareRequestFilter.java:91)
    at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)
    at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
    at org.springframework.security.util.FilterChainProxy.doFilter(FilterChainProxy.java:175)
    at org.springframework.security.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:99)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.pentaho.platform.web.http.filters.SystemStatusFilter.doFilter(SystemStatusFilter.java:60)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.pentaho.platform.web.http.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:113)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:470)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Thread.java:662)
    Caused by: org.pentaho.platform.plugin.action.kettle.KettleComponentException: Kettle.ERROR_0014 - [es_249] Error occurred during transformation execution
    at org.pentaho.platform.plugin.action.kettle.KettleComponent.executeJob(KettleComponent.java:872)
    ... 76 more

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

    Default

    That list is quite impressive, mostly follow-up errors I think.
    I would isolate the date-format-problem from the rest.
    Set up a trivial transformation (Datagrid, Excel Writer) and play with it.
    Or take the attached one - it's working over here.
    Attached Files Attached Files
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by dulci View Post
    Caused by: java.lang.ClassNotFoundException: org.apache.poi.ss.util.CellUtil
    Do you have all the libraries installed into your BIServer correctly?

    Protip: Test the transform on Spoon, then try with kitchen, and lastly install it into you BIServer.

  6. #6

    Default

    Hi, same problem for me. If i run the job from PDI it's ok, if i run the job from xaction i catch the java.lang.NoClassDefFoundError: org/apache/poi/ss/util/CellUtil error.

    Did you fix it?

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.