Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Output to multiple Excel sheets

  1. #1
    Join Date
    Dec 2016
    Posts
    27

    Default Output to multiple Excel sheets

    Hi,

    A colleague caught me on Friday and presented an Excel sheet containing financial data for 100 companies. Each company is on a different tab. Their request is for an Excel sheet split by financial line item instead. So for example instead of 100 sheets, 1 per company ("Vodafone", "Coca Cola" etc) there would be ~20 sheets, 1 per financial line item ("Net Assets", "Earnings per Share" etc).

    I can get much of the way with Pentaho - i.e. I now have one clean normalized table, but I can't figure out a way to split by sheet without turning to VBA.

    Is there another way?

    Thanks,

    Andy

  2. #2
    Join Date
    Feb 2017
    Posts
    7

    Default

    Have you tried excel writer?

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

    Default

    Excel-Output does expand a variable sheetname, so all you need is a way to setup a variable.
    I guess I would add the sheetname to the normalized rows and let Text-File-Output create temporary CSV files using the sheetname as filename somehow - TFO can read the filename from a field.
    In another trasnsformation I would use Get-File-Names and Copy-Rows-To-Result to create the control set.
    Eventually, I would loop over the control set in a last transformation using Text-File-Input and Excel-Output.
    Options "Execute for every input row" and "Copy previous results to parameters" come to mind.
    Some details withheld.
    Shouldn't be that difficult to implement.
    Good luck.
    So long, and thanks for all the fish.

  4. #4
    Join Date
    Dec 2016
    Posts
    27

    Default

    Hi,

    Thanks for your reply marabu. It's slightly overwhelming to be honest - I'll need to experiment, but I'm glad to hear that structurally this is do-able. You'll recall helping me with my first questions a month ago so this is shifting me up a gear in terms of capability in this application. I'll see how I get on with this but I think I'll have to come back to it and find another solution for now.


    What I have now:

    Name:  co_transform.jpg
Views: 571
Size:  31.0 KB

    I've tried generating a sequence of csvs but no joy. Cold this be down to the spaces i n my 'Measure' descriptions?

    Thanks,


    Andy

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

    Default

    You really overestimate my eyesight - I'm not hawkeye, I'm marabu, after all.


    But here is a demo job picking up senior centers published by the City of Chicago on their Open Data Portal.
    One transformation dispatches rows to separate CSV files for each center.
    The other transformation creates and populates a sheet for each center.
    Have fun analyzing.

    BTW: I like the magnetic grid feature (Tools menu, Options, Look & Feel) very much. Try canvas grid size 16.


    UPDATE: I'm not allowed to post demos anymore, it seems.
    Last edited by marabu; 02-05-2017 at 06:28 AM.
    So long, and thanks for all the fish.

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

    Default

    Since the Application Firewall won't let me attach the demo job to the existing post, I'll try to use a fresh post.
    Let's see how far this will get us ...

    BINGO
    Attached Files Attached Files
    So long, and thanks for all the fish.

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.