Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Manipulating Excel files via Spoon

  1. #1
    Join Date
    Dec 2015
    Posts
    1

    Default Manipulating Excel files via Spoon

    Hi, I have a database with products and stores, and I want to make an Excel file with the products as rows and the stores as columns, something like this:

    store1 store2
    product1
    product2

    Having the products as rows is easy, I use the Table input and write the result with Microsoft Excel Output. My problem is with the stores, since I need to be dynamic, sometimes there can be 2 stores, sometimes 10... and I want to write X stores, one in the next column. Is there a way to do this with Spoon?
    I was thinking with some Macros in the Excel, but I can't find if it's possible to run some VBA code somewhere in Spoon.

    Thanks.

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

    Default

    I don't think that you can run Macros.
    But I would have a look at the Metadata steps.
    There are few blogs about it and there is a sample within the data-integration folder (samples folder)
    -- Mick --

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

    Default

    I see a metadata injection step in someone's future....

  4. #4
    Join Date
    Aug 2011
    Posts
    360

    Default

    Hi,

    If you want to produce some kind of cross table into Excel, maybe the best way
    is to use pentaho reporting, with data comming from your transformation.
    Or you can do that (we that some time):
    Make an excel template with a sheet Data and a sheet Report. In the report sheet, make a crosstab which dataset
    is defined by a formula taking all data from the sheet Data.
    In PDI, write your already aggregated data to the sheet Data. You wull have your report already done in the excel file

  5. #5

    Default

    PDI's integration with Excel is not so good. Even the Excel output transform is limited to the old XLS format and limited to 65,535 rows of data.
    You will need to create a plug-in to be able to execute a macro in the XLM file.
    SourceForge has the Java code that will accomplish that.
    Good luck.

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

    Default

    Horses for corses!
    You can use Excel Writer step and select teh latest Excel format .xlsx
    In my view, executing a macro in Excel should not be done by an ETL tool.

    Why using a macro when you have at your disposal a "proper" tool for manipulating data?
    -- Mick --

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

    Default

    Quote Originally Posted by marksimms View Post
    is limited to the old XLS format and limited to 65,535 rows of data.
    100% not true. I use PDI to write XLSX files all the time. and XLSX files are not limited to the 65535 rows.

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.