Searching One Column, Return Contents of Another

PowerShell Help
1

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

 

2

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

3

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
4

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.