Hitachi Vantara Pentaho Community Forums
Results 1 to 13 of 13

Thread: Cannot append spreadsheet to .xlsx file.

  1. #1
    Join Date
    Sep 2011
    Posts
    171

    Default Cannot append spreadsheet to .xlsx file.

    Hello Everyone!

    I get the following error when I try to output to excel file (.xlsx format) and not just output, but append a new worksheet.

    I attached the transformation. Anybody has seen it before?

    Regards,
    Alex



    2011/09/29 10:14:15 - Spoon - Transformation opened.
    2011/09/29 10:14:15 - Spoon - Launching transformation [FromSQLToExcel]...
    2011/09/29 10:14:15 - Spoon - Started the transformation execution.
    2011/09/29 10:14:15 - FromSQLToExcel - Dispatching started for transformation [FromSQLToExcel]
    2011/09/29 10:14:15 - Transformation metadata - Natural sort of steps executed in 0 ms (3 time previous steps calculated)
    2011/09/29 10:14:15 - Microsoft Excel Output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error opening new file
    2011/09/29 10:14:15 - Microsoft Excel Output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : jxl.read.biff.BiffException: Unable to recognize OLE stream
    2011/09/29 10:14:15 - Microsoft Excel Output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at jxl.read.biff.CompoundFile.<init>(CompoundFile.java:116)
    2011/09/29 10:14:15 - Microsoft Excel Output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at jxl.read.biff.File.<init>(File.java:127)
    2011/09/29 10:14:15 - Microsoft Excel Output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at jxl.Workbook.getWorkbook(Workbook.java:221)
    2011/09/29 10:14:15 - Microsoft Excel Output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at jxl.Workbook.getWorkbook(Workbook.java:198)
    2011/09/29 10:14:15 - Microsoft Excel Output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.exceloutput.ExcelOutput.openNewFile(ExcelOutput.java:522)
    2011/09/29 10:14:15 - Microsoft Excel Output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.exceloutput.ExcelOutput.init(ExcelOutput.java:727)
    2011/09/29 10:14:15 - Microsoft Excel Output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.step.StepInitThread.run(StepInitThread.java:52)
    2011/09/29 10:14:15 - Microsoft Excel Output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at java.lang.Thread.run(Unknown Source)
    2011/09/29 10:14:15 - Microsoft Excel Output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Couldn't open file N:\Daily Positions\State Street\Pentaho\MyProcessMultipleFilesCashPositions\LoadingIntoExcel\OutputFromDB.xlsx
    2011/09/29 10:14:15 - Microsoft Excel Output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error initializing step [Microsoft Excel Output]
    2011/09/29 10:14:15 - FromSQLToExcel - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Step [Microsoft Excel Output.0] failed to initialize!
    2011/09/29 10:14:15 - Table input.0 - Finished reading query, closing connection.
    2011/09/29 10:14:15 - Spoon - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : FromSQLToExcel: preparing transformation execution failed
    2011/09/29 10:14:15 - Spoon - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : org.pentaho.di.core.exception.KettleException:
    2011/09/29 10:14:15 - Spoon - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : We failed to initialize at least one step. Execution can not begin!
    2011/09/29 10:14:15 - Spoon - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2011/09/29 10:14:15 - Spoon - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2011/09/29 10:14:15 - Spoon - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.Trans.prepareExecution(Trans.java:792)
    2011/09/29 10:14:15 - Spoon - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.ui.spoon.trans.TransGraph$22.run(TransGraph.java:3659)
    2011/09/29 10:14:15 - Spoon - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at java.lang.Thread.run(Unknown Source)
    2011/09/29 10:14:15 - FromSQLToExcel - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors detected!
    2011/09/29 10:14:15 - FromSQLToExcel - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors detected!
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2009
    Posts
    810

    Default

    Hi there,

    it seems you are using the Excel Output step. It does not support XLSX files. You need to use the Excel Writer step instead if you want to generate or append to xlsx files.

    Cheers
    Slawo

  3. #3
    Join Date
    Sep 2011
    Posts
    171

    Default

    Thank you, Slawo!

    I tried Excel Writer. I chosen option "Use existing file for writing" and chosen file "filename.xlsx" and as a output I got "filename.xlsx.xlsx" Am I missing something?

    I also tried deleting extension in the field "Filename", but then I got as an output "filename_0.xlsx"

    Regards,
    Alex

  4. #4
    Join Date
    Sep 2011
    Posts
    171

    Default

    I am going to wait for a little bit more and will file JIRA report. It looks like bug to me!

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

    Default

    In the filename box, leave off the extension...

    That's how I've always done it.

  6. #6
    Join Date
    Sep 2011
    Posts
    171

    Default

    In case I write just file name like "ExcelFileName" (i.e. without .xlsx) I get back

    "ExcelFileName_0.xlsx"

    Also, I noticed that column name and date formatting do not show.

  7. #7
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Hi.
    How do you create your file name? Do you actually enter the name or use any variable?
    Could it be that you had that file already open from previous tests and Excel Writer added _0 because it could not write original name?

    I used this step and did not have same issue (I know it does not help!)

    Which option have you checked/uncheked?

    Mick

  8. #8
    Join Date
    Sep 2009
    Posts
    810

    Default

    Sounds to me like "Inlcude step copy in file name" was checked. Hence the extra 0.

    Cheers
    Slawo

  9. #9
    Join Date
    Sep 2011
    Posts
    171

    Default

    Indeed "Include step copy in file name" was checked. Thank you!

    Now I realized that column names and formatting (for date) do not go through.

    I attached simple example.

    Regards,
    Alex
    Attached Files Attached Files

  10. #10
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Hi.
    For Date formatting I would point your Excel file to a Template and set that column to Date and choose the desired format.
    It *should* work :-)

    Mick

  11. #11
    Join Date
    Sep 2011
    Posts
    171

    Default

    Thank you, Mick!

    I tried to use InputExcel.xlsx (see my previous attachment) as template file, but got the following error message:
    It seems like it cannot find the worksheet, while I double checked that worksheet's name is spelled correctly.

    I am still curious why column names do not flow through?

    Regards,
    Alex


    2011/09/30 10:50:00 - Spoon - Transformation opened.
    2011/09/30 10:50:00 - Spoon - Launching transformation [TestExcelWriter]...
    2011/09/30 10:50:00 - Spoon - Started the transformation execution.
    2011/09/30 10:50:00 - TestExcelWriter - Dispatching started for transformation [TestExcelWriter]
    2011/09/30 10:50:00 - Transformation metadata - Natural sort of steps executed in 0 ms (2 time previous steps calculated)
    2011/09/30 10:50:00 - Microsoft Excel Writer.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Error opening new file
    2011/09/30 10:50:00 - Microsoft Excel Writer.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : org.pentaho.di.core.exception.KettleException:
    2011/09/30 10:50:00 - Microsoft Excel Writer.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Tempate Sheet: FromPentaho not found, aborting
    2011/09/30 10:50:00 - Microsoft Excel Writer.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
    2011/09/30 10:50:00 - Microsoft Excel Writer.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.prepareNextOutputFile(ExcelWriterStep.java:688)
    2011/09/30 10:50:00 - Microsoft Excel Writer.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.init(ExcelWriterStep.java:862)
    2011/09/30 10:50:00 - Microsoft Excel Writer.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.step.StepInitThread.run(StepInitThread.java:52)
    2011/09/30 10:50:00 - Microsoft Excel Writer.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at java.lang.Thread.run(Unknown Source)
    2011/09/30 10:50:00 - Microsoft Excel Writer.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Couldn't prepare output file N:\Daily Positions\State Street\Pentaho\MyProcessMultipleFilesCashPositions\LoadingIntoExcel\TestExcelWriter\OutputExcel
    2011/09/30 10:50:00 - Microsoft Excel Input.0 - Finished processing (I=1, O=0, R=0, W=0, U=0, E=0)
    2011/09/30 10:50:00 - Microsoft Excel Writer.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=1)
    2011/09/30 10:50:00 - Spoon - The transformation has finished!!
    2011/09/30 10:50:00 - TestExcelWriter - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors detected!
    2011/09/30 10:50:00 - TestExcelWriter - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Errors detected!
    2011/09/30 10:50:00 - TestExcelWriter - TestExcelWriter
    2011/09/30 10:50:00 - TestExcelWriter - TestExcelWriter

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

    Default

    In the content tab, you haven't specified any of your fields to be added / formatted into your excel file - that might have something to do with the issues you are describing...
    ---
    InputExcel isn't in your RAR, so it's a lot more difficult to troubleshoot.

    Forum Pro Tip: For file Input and File Output for demo cases being posted to forum, include your files in the Zip/Rar, but refer to your files in your KTR / KJB with ${Internal.Transformation.Filename.Directory} (For KTR) and ${Internal.Job.Filename.Directory} (for KJB) We won't need to have the same filesystem layout as you if you do this.
    ie. In Excel Input, instead of N:\full\path\to\excel\Input.xlsx use ${Internal.Transformation.Filename.Directory}\Input.xlsx and include put the XLSX file in the same folder as your KTR file.

  13. #13
    Join Date
    Sep 2011
    Posts
    171

    Default

    Thank you!

    You are absolutely right, I forgot to specify the column name under Content tab. There is no "Get Fields" button, so I missed it.
    I did not get the template to work. I managed to accomplish what I need it.

    Regards,
    Alex

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.