Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Split field with names and values

  1. #1

    Default Split field with names and values

    Hello,
    I have a "simple" problem ... in an excel file I have a column let us call it "metadata", inside this column there is a string that contain some "field name" followed by its values in this form:

    Last value: 5;Last sum: 15;Previous value: 23 and so on

    The format is quite simple in the Excel column taht I need to parse, the field name is followed by a colon and the semicolon is the fileds separator, but ... a row can contain the name/value pairs in different order and some row can contain different name/value pairs.

    For example:
    Last value: 5;Last sum: 15;Previous value: 23;Edited: 23/04/2012;Created: 12/12/2012
    Last value: 2;Previous value: 12;Edited: 20/04/2012;Created: 07/12/2012;Last sum: 1
    Previous value: 12;Edited: 20/04/2012;Created: 07/12/2012

    In a few world:
    - the field names are unknown
    - the order is not fixed
    - some fields can be present or not

    I need to process this excel field and obtain a new file where the column "metadata" is correctly splitted and grouped. So if the excel metadata column contain:

    Last value: 5;Last sum: 15;Previous value: 23;Edited: 23/04/2012;Created: 12/12/2012
    Last value: 2;Previous value: 12;Edited: 20/04/2012;Created: 07/12/2012;Last sum: 1
    Previous value: 4;Edited: 17/04/2012;Last Value:3

    I need to have as result something like:

    Last value: 5;Last sum: 15;Previous value: 23;Edited: 23/04/2012;Created: 12/12/2012;
    Last value: 2;Last sum: 1;Previous value: 12;Edited: 20/04/2012;Created: 07/12/2012;
    Last Value: 3;Last sum: ;Previous value: 4;Edited: 17/04/2012;Created: ;


    i.e. all the fields in order (no matter what order) and the eventually missing field "filled" with and "empty value" or costant sring (no matter) ...

    I think that the problem can be summarized as: how can I extract field and values name from a file where every row contain name/values pairs and the name is basically unknow?

    Thank to everyone can suggest a solution ...

    Regards
    Danilo

  2. #2
    Join Date
    Nov 1999
    Posts
    459

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.