Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Write sorted rows output into multiple excel files

  1. #1

    Default Write sorted rows output into multiple excel files

    Hi

    I have a transformation, which sorts data in ascending order based on multiple fields.
    Now while writing these rows into excel output file, I want to create 3 output excel files. Rows should be evenly distributed in all 3 output excel files.
    Rows should be in sorted order in these output files.

    Please help!!

    Thanks

  2. #2
    Join Date
    Aug 2016
    Posts
    235

    Default

    Quote Originally Posted by Ajinkya View Post
    Hi

    I have a transformation, which sorts data in ascending order based on multiple fields.
    Now while writing these rows into excel output file, I want to create 3 output excel files. Rows should be evenly distributed in all 3 output excel files.
    Rows should be in sorted order in these output files.

    Please help!!

    Thanks
    If I understand the excel output steps correctly, they only support file name reference as static value or variable. So you can't insert a file name field into the stream.

    If this assumption is correct, I suggest duplicating the excel output step so you have 3 of them in total. Then insert the file names as you wish in each one, either as a static value or a variable that can be set outside the transformation.

    1) Sort rows, set output stream to "round-robin" which means distribute. You can see the data-movement on the hops between steps. A normal line means "round-robin" (distribute), while step that copies the stream to all outgoing hops have a specific icon on the hop. Right-click the step, go to "Data-movement..." and switch between "Round-robin" and "Copy data to next steps" to see the difference.
    2) Send one hop from sort rows step to each of the excel output steps.

    Done!

  3. #3

    Default

    would distributing output stream from sort step to 3 excel output steps work? I think this will not write the records in sorted order in the output excel files.
    I want the data written in all the 3 files to be in sorted form. For example: excel output file 1 will have data say from A to H, excel output file 2 will have data from I to P and third file will have Q to Z.

  4. #4
    Join Date
    Aug 2016
    Posts
    235

    Default

    Ok, according to that specification, no it will not work. It would be sorted, but it would be sorted like this:

    file 1) A, D, G
    file 2) B, E, H
    file 3) C, F, I

    However according to your latest post, you want them sorted like this:

    file 1) A, B ,C
    file 2) D, E, F
    file 3) G, H, I

    This is also possible, but it would require a different and more technical approach. You would need to count all rows so you know where to split. Then you would need to add a field in each row with the batch number (1,2,3). Then you would need a switch / case to split the stream to each separate excel output accordingly.

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 - 2017 Pentaho Corporation. All Rights Reserved.