Searching One Column, Return Contents of Another

Welcome Forums General PowerShell Q&A Searching One Column, Return Contents of Another

This topic contains 3 replies, has 3 voices, and was last updated by

 
Participant
2 days, 19 hours ago.

  • Author
    Posts
  • #128464

    Participant
    Points: 19
    Rank: Member

    Hi all,

    PowerShell beginner here, I have been going round in circles with this for a few hours and getting nowhere.

    I have a large CSV file, multiple rows/columns, but for my issue I am only concerned with three

    In column one I have a list of parent items.

    In column two I have a list of child items.

    In column three is a party responsible for each parent item

    An item can be both a parent and a child but not of itself, a parent may not have any children.

    I am trying to find the responsible party for both my selected parents and all of their children.

     

    I have a regular expression to match the parent items I am concerned with and am able to retrieve and split the child items into an array which reads.

    Parent

    Child

    Child

    Parent

    Child

    ...

     

    I am struggling to search the CSV column one for the list of items in my array and return the contents of column three the responsible party on match.  I feel like this should be straight forward enough but I have just hit a wall.

    Thanks in advance for any suggestions

     

  • #128479

    Participant
    Points: 832
    Helping Hand
    Rank: Major Contributor

    We are happy to help you, can you share a sample csv, if possible the code that you have so far as well.

  • #128487

    Participant
    Points: 305
    Helping Hand
    Rank: Contributor

    Using this as an example:

    $csv = @()
    $csv += [pscustomobject]@{Parent='Parent1';Child='Child1';Owner='John'}
    $csv += [pscustomobject]@{Parent='Parent2';Child='Child8';Owner='Sally'}
    $csv += [pscustomobject]@{Parent='Parent2';Child='Child3';Owner='Frank'}
    $csv += [pscustomobject]@{Parent='Parent1';Child='Child10';Owner='Sally'}
    $csv += [pscustomobject]@{Parent='Parent1';Child='Child6';Owner='Derek'}
    $csv += [pscustomobject]@{Parent='Parent2';Child='Child4';Owner='Julie'}
    
    #Grouping
    $parentGrouped = $csv | Group-Object -Property Parent
    
    #Where clause
    $csv | Where{$_.Parent -eq 'Parent2'} | Select Owner
    

    Walk thru grouping...

    PS C:\Users\Rob> $parentGrouped
    
    Count Name                      Group                                                                                                                                                                       
    ----- ----                      -----                                                                                                                                                                       
        3 Parent1                   {@{Parent=Parent1; Child=Child1; Owner=John}, @{Parent=Parent1; Child=Child10; Owner=Sally}, @{Parent=Parent1; Child=Child6; Owner=Derek}}                                  
        3 Parent2                   {@{Parent=Parent2; Child=Child8; Owner=Sally}, @{Parent=Parent2; Child=Child3; Owner=Frank}, @{Parent=Parent2; Child=Child4; Owner=Julie}}                                  
    
    
    PS C:\Users\Rob> $parentGrouped | Where{$_.Name -eq 'Parent2'}
    
    Count Name                      Group                                                                                                                                                                       
    ----- ----                      -----                                                                                                                                                                       
        3 Parent2                   {@{Parent=Parent2; Child=Child8; Owner=Sally}, @{Parent=Parent2; Child=Child3; Owner=Frank}, @{Parent=Parent2; Child=Child4; Owner=Julie}}                                  
    
    
    PS C:\Users\Rob> ($parentGrouped | Where{$_.Name -eq 'Parent2'}).Group
    
    Parent  Child  Owner
    ------  -----  -----
    Parent2 Child8 Sally
    Parent2 Child3 Frank
    Parent2 Child4 Julie
    
    
    PS C:\Users\Rob> ($parentGrouped | Where{$_.Name -eq 'Parent2'}).Group.Owner
    Sally
    Frank
    Julie
    

    A simple query and return owners...

    PS C:\Users\Rob> $csv | Where{$_.Parent -eq 'Parent2'} | Select Owner
    
    Owner
    -----
    Sally
    Frank
    Julie
    
    • #128915

      Participant
      Points: 19
      Rank: Member

      Thanks Rob, after taking a look through your examples and running them myself I found where I was going wrong and got to where I needed to be, appreciate the help.

You must be logged in to reply to this topic.