Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: How to extract multiple values in a Column and create a new row for each?

  1. #1

    Question How to extract multiple values in a Column and create a new row for each?

    I have a CSV file that has mulitple values that I would like to create separate rows. To keep it simple suppose I have 2 columns:

    Hostname, Installed_Applications
    *Installed_Application has a list of Applications with a Version number which are separated by | (pipe)

    Hostname = server123

    Installed_Applications = BMC Atrium Discovery Proxy 10.0.0.3 | 10.0.0.3
    IBM Endpoint Manager Client | 9.1.1117.0
    Microsoft .NET Framework 4.5.1 | 4.5.50938
    Microsoft Visual C++ 2008 Redistributable - x64 9.0.30729.6161 | 9.0.30729.6161
    Microsoft Visual C++ 2008 Redistributable - x86 9.0.21022.218 | 9.0.21022.218
    Microsoft Visual C++ 2008 Redistributable - x86 9.0.30729.4148 | 9.0.30729.4148
    VMware Tools | 9.4.0.1280544

    I would like to create for every Installed Application - represent it in this way Hostname,Installed_Application,Version:

    server123,BMC Atrium Discovery Proxy 10.0.0.3,10.0.0.3
    server123,IBM Endpoint Manager Client,9.1.1117.0
    server123,Microsoft .NET Framework 4.5.1,4.5.50938
    ...

    Please advise. Thanks for the support in advance!!

    KP

  2. #2

    Default

    Hi KP,

    PFA Zip folder.

    Thanks,
    Malay
    Attached Files Attached Files

  3. #3

    Default

    Malay -

    Thank you for the response and the transform!! After reviewing the transform, I think I was not clear on my scenario. I think the transform you wrote assumes that the CSV file has each separate Installed_Application already defined for a server. The issue I face, is that there is only 1 row with a Hostname, but multiple values for (ProductName,ProductDisplayName,ProductVersion,Manufacturer) in Installed_Applications all lumped together. So I would like a separate line for each product and provide the Hostname for each row. The example below may make the requirement clearer.

    I have provided another column that provides XML type keys which might make it easier to separate the values (ProductName,ProductDisplayName,ProductVersion,Manufacturer).

    In my CSV, here is 1 row of data. There are 2 Columns:

    Hostname,Installed_Application
    CDM-AD,"<Key><Name>BMC Atrium Discovery Proxy_is1</Name><DisplayName>BMC Atrium Discovery Proxy 10.0.0.3</DisplayName><DisplayVersion>10.0.0.3</DisplayVersion><Publisher>BMC Software</Publisher></Key>
    <Key><Name>{1F1C2DFC-2D24-3E06-BCB8-725134ADF989}</Name><DisplayName>Microsoft Visual C++ 2008 Redistributable - x86 9.0.30729.4148</DisplayName><DisplayVersion>9.0.30729.4148</DisplayVersion><Publisher>Microsoft Corporation</Publisher></Key>
    <Key><Name>{92FB6C44-E685-45AD-9B20-CADF4CABA132}.KB2894854v2</Name><DisplayName>Security Update for Microsoft .NET Framework 4.5.1 (KB2894854v2)</DisplayName><DisplayVersion>2</DisplayVersion><Publisher>Microsoft Corporation</Publisher></Key>
    <Key><Name>{92FB6C44-E685-45AD-9B20-CADF4CABA132}.KB2898869</Name><DisplayName>Security Update for Microsoft .NET Framework 4.5.1 (KB2898869)</DisplayName><DisplayVersion>1</DisplayVersion><Publisher>Microsoft Corporation</Publisher></Key>
    <Key><Name>{92FB6C44-E685-45AD-9B20-CADF4CABA132}.KB2901126</Name><DisplayName>Security Update for Microsoft .NET Framework 4.5.1 (KB2901126)</DisplayName><DisplayVersion>1</DisplayVersion><Publisher>Microsoft Corporation</Publisher></Key>
    <Key><Name>{92FB6C44-E685-45AD-9B20-CADF4CABA132}.KB2931368</Name><DisplayName>Security Update for Microsoft .NET Framework 4.5.1 (KB2931368)</DisplayName><DisplayVersion>1</DisplayVersion><Publisher>Microsoft Corporation</Publisher></Key>
    <Key><Name>{92FB6C44-E685-45AD-9B20-CADF4CABA132}.KB2972216</Name><DisplayName>Security Update for Microsoft .NET Framework 4.5.1 (KB2972216)</DisplayName><DisplayVersion>1</DisplayVersion><Publisher>Microsoft Corporation</Publisher></Key>
    <Key><Name>{C43A01F0-D4DB-4CA3-9DF6-7DF629BBCCD4}</Name><DisplayName>IBM Endpoint Manager Client</DisplayName><DisplayVersion>9.1.1117.0</DisplayVersion><Publisher>IBM Corp.</Publisher></Key>

    So for each ROW, there is no consistent number of values, some servers may have more installed applications than others. So I would like to create the following:

    Hostname,ProductName,ProductDisplayName,ProductVersion,Manufacturer
    CDM-AD,BMC Atrium Discovery Proxy_is1,BMC Atrium Discovery Proxy 10.0.0.3,10.0.0.3,BMC Software
    CDM-AD,{1F1C2DFC-2D24-3E06-BCB8-725134ADF989},Microsoft Visual C++ 2008 Redistributable - x86 9.0.30729.4148,9.0.30729.4148,Microsoft Corporation
    CDM-AD,{92FB6C44-E685-45AD-9B20-CADF4CABA132}.KB2894854v2,Security Update for Microsoft .NET Framework 4.5.1 (KB2894854v2),2,Microsoft Corporation
    CDM-AD,{92FB6C44-E685-45AD-9B20-CADF4CABA132}.KB2898869,Security Update for Microsoft .NET Framework 4.5.1 (KB2898869),1,Microsoft Corporation
    CDM-AD,{92FB6C44-E685-45AD-9B20-CADF4CABA132}.KB2901126,Security Update for Microsoft .NET Framework 4.5.1 (KB2901126),1,Microsoft Corporation
    CDM-AD,{92FB6C44-E685-45AD-9B20-CADF4CABA132}.KB2931368,Security Update for Microsoft .NET Framework 4.5.1 (KB2931368),1,Microsoft Corporation
    CDM-AD,{92FB6C44-E685-45AD-9B20-CADF4CABA132}.KB2972216,Security Update for Microsoft .NET Framework 4.5.1 (KB2972216),1,Microsoft Corporation
    CDM-AD,{C43A01F0-D4DB-4CA3-9DF6-7DF629BBCCD4},IBM Endpoint Manager Client,9.1.1117.0,IBM Corp.
    ...

    I hope this helps with the requirement. Thank you again for the support!!

    KP

  4. #4
    Join Date
    Mar 2013
    Posts
    127

    Default

    Hi karlis

    Please try attached transformation and with csv contents below
    CDM-AD,"<Key><Name>BMC Atrium Discovery Proxy_is1</Name><DisplayName>BMC Atrium Discovery Proxy 10.0.0.3</DisplayName><DisplayVersion>10.0.0.3</DisplayVersion><Publisher>BMC Software</Publisher></Key>
    <Key><Name>{1F1C2DFC-2D24-3E06-BCB8-725134ADF989}</Name><DisplayName>Microsoft Visual C++ 2008 Redistributable - x86 9.0.30729.4148</DisplayName><DisplayVersion>9.0.30729.4148</DisplayVersion><Publisher>Microsoft Corporation</Publisher></Key>
    <Key><Name>{92FB6C44-E685-45AD-9B20-CADF4CABA132}.KB2894854v2</Name><DisplayName>Security Update for Microsoft .NET Framework 4.5.1 (KB2894854v2)</DisplayName><DisplayVersion>2</DisplayVersion><Publisher>Microsoft Corporation</Publisher></Key>
    <Key><Name>{92FB6C44-E685-45AD-9B20-CADF4CABA132}.KB2898869</Name><DisplayName>Security Update for Microsoft .NET Framework 4.5.1 (KB2898869)</DisplayName><DisplayVersion>1</DisplayVersion><Publisher>Microsoft Corporation</Publisher></Key>
    <Key><Name>{92FB6C44-E685-45AD-9B20-CADF4CABA132}.KB2901126</Name><DisplayName>Security Update for Microsoft .NET Framework 4.5.1 (KB2901126)</DisplayName><DisplayVersion>1</DisplayVersion><Publisher>Microsoft Corporation</Publisher></Key>
    <Key><Name>{92FB6C44-E685-45AD-9B20-CADF4CABA132}.KB2931368</Name><DisplayName>Security Update for Microsoft .NET Framework 4.5.1 (KB2931368)</DisplayName><DisplayVersion>1</DisplayVersion><Publisher>Microsoft Corporation</Publisher></Key>
    <Key><Name>{92FB6C44-E685-45AD-9B20-CADF4CABA132}.KB2972216</Name><DisplayName>Security Update for Microsoft .NET Framework 4.5.1 (KB2972216)</DisplayName><DisplayVersion>1</DisplayVersion><Publisher>Microsoft Corporation</Publisher></Key>
    <Key><Name>{C43A01F0-D4DB-4CA3-9DF6-7DF629BBCCD4}</Name><DisplayName>IBM Endpoint Manager Client</DisplayName><DisplayVersion>9.1.1117.0</DisplayVersion><Publisher>IBM Corp.</Publisher></Key>"



    Regards,
    Mateen
    Attached Files Attached Files

  5. #5

    Default

    Mateen -

    Sorry for the late response. I tested this and it worked GREAT!

    Thank you for the support!

    Karlis

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.