Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Normalize CSV table with strange format

  1. #1
    Join Date
    Aug 2017
    Posts
    5

    Default Normalize CSV table with strange format

    Hi all,

    I have a CSV formatted in a rather strange way (like an table format maybe?). I want to combine the field names after the 2nd with rows succeeding the 2nd row, all while preserving the first 2 field names as separate columns. Specifically,
    I want to change this table:

    ID XXX 00:30 01:00 01:30 02:00
    1 x val_01 val_02 val_03 val_04
    2 x val_05 val_06 val_07 val_08
    3 y val_09 val_10 val_11 val_12







    to this:

    1_00:30 ID x val_01
    1_01:00 ID x val_02
    1_01:30 ID x val_03
    1_02:00 ID x val_04
    2_00:30 ID x val_05
    2_01:00 ID x val_06
    2_01:30 ID x val_07
    2_02:00 ID x val_08
    3_00:30 ID y val_09
    3_01:00 ID y val_10
    3_01:30 ID y val_11
    3_02:00 ID y val_12



















    Any ideas on how to do this?

  2. #2
    Join Date
    Aug 2017
    Posts
    5

    Default Normalize CSV table with strange format

    Hi all,

    I have a csv given in a rather strange format. I would like to normalize the data combining field names with rows,
    dynamically (the number of rows of a given file can change file to file).
    Additionally, the first column name is not a label of the field: it is an independent piece of data.

    Specifically, I want to change a table like this:

    NAME XXX 00:00 00:30 01:00 01:30
    1 x VAL_01 VAL_02 VAL_03 VAL_04
    2 y VAL_05 VAL_06 VAL_07 VAL_08






    to a table like this:

    1_00:00 NAME x VAL_01
    1_00:30 NAME x VAL_02
    1_01:00 NAME x VAL_03
    1_01:30 NAME x VAL_04
    2_00:00 NAME y VAL_05
    2_00:30 NAME y VAL_06
    2_01:00 NAME y VAL_07
    2_01:30 NAME y VAL_08















    I have looked at this dynamic solution by Matt Casters: http://www.ibridge.be/?p=194

    However it doesn't solve the concatenation of the field names with rows, or the additional column XXX.

    Any ideas on how to accomplish this?
    Last edited by damartian; 10-06-2017 at 05:08 AM. Reason: added that solution needs to be dynamic

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

    Default

    Here's a demo for starters.
    Attached Files Attached Files
    So long, and thanks for all the fish.

  4. #4
    Join Date
    Aug 2017
    Posts
    5

    Default

    Beautiful! It works perfectly, thank you so much!

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.