Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Excel input with merged cells

  1. #1

    Default Excel input with merged cells

    Hi everybody,

    I have an Excel file with merged cells.
    Is there a way to make PDI not consider cells with null value? I mean, load the file as if it has no merged cells.

    Thanks!

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

    Default

    Merged cells are represented by the upper left cell, the remaining cells are cleared.
    That's an Excel definition and the Java libraries used by Kettle handle it that way.
    If you want to shift fields or something like that, you will have to implement it yourself.
    So long, and thanks for all the fish.

  3. #3

    Default

    Thanks for the reply.
    I'm trying to achieve this by grouping and assigning the value of the first cell of the group. I need something like the lag of Analytic Query but I need the "N" value to be dinamic.

    Any ideas?

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

    Default

    Maybe you can create a variable and use a variable as your argument for the (N) value?
    -- Mick --

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

    Default

    Kettle doesn't know about the Excel concept of merged cells, all you get is rows and fields with some fields having no value.
    Eventually you will replicate the merge value to succeeding consecutive empty fields, horizontally or vertically, or even both directions.
    For a generic solution you must devise a configuration scheme, telling your transformation where to find the merge value for each empty field encountered and considered part of a merge block.
    It's much easier, if you just want to replicate vertically: Set Repeat=Y in the Excel Input field configuration.
    Explicit horizontal replication can be done with a "User Defined Java Expression".
    So long, and thanks for all the fish.

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.