PowerShell Compare-Object to produce change report file

Question

I've looked at numerous examples and have made headway in producing a change report. But, I'm stuck in one area. Here's the scenario...

File 1 CSV file sample data
ID,Name,Location,Gender
1,Peter,USA,Male
2,Paul,UK,Male
3,Mary,PI,Female

File 2 CSV file sample data (No ID column)
Name,Location,Gender
Peter,USA,Female
Paul,UK,Male
Mary,USA,Female
Tom,PI,Female
Barry,CAN,Male

File 2 has changes and additions, i.e. Peter turned female, Mary moved to the US, both Tom and Barry are the new people. Change report output file contain what the changes are. Problem is, I can't figure out how to get the ID for both Peter and Mary from File 1, into my Change Report. ID is always empty Here's my code...(I hope someone can shed some light. Thanks in advance.)

$MyCSVFields = @('Name','Location','Gender')
$CompareResults = Compare-Object $RefObj $DffObj -Property $MyCSVFields -IncludeEqual
$NewOrChangedData = @()
Foreach($Row in $CompareResults)
{
    if( $Row.SideIndicator -eq "=>" )
    {                
        $TempObject = [pscustomobject][ordered] @{
            ID = $Row.ID
            Name = $Row.Name
            Location = $Row.Location
            Gender = $Row.Gender
            #Sanity check "Compare Indicator" = $Row.SideIndicator 
        }
        $NewOrChangedData += $TempObject
    }
}

Thanks to Theo for providing an understanding of how to use the Where-Object. Here is the updated code that keeps it simple for beginners and still works for us.

Foreach($Row in $CompareResults)
{
    if( $Row.SideIndicator -eq "=>" )
    {                
        $myOrgID = $RefObj | Where-Object Name -eq $Row.Name
        $TempObject = [pscustomobject][ordered] @{
            ID = $myOrgID.ID
            Name = $Row.Name
            Location = $Row.Location
            Gender = $Row.Gender
            #Sanity check "Compare Indicator" = $Row.SideIndicator 
        }
        $NewOrChangedData += $TempObject
    }
}

Have you tried the -passthru option of compare-object? Or look at the inputobject property returned. — js2010, Jun 18 '20 at 18:52
@js2010 Tried a bunch of stuff. Theo provided the clue and answer using the Where-Object. So, in my code, just to keep the readability easy for beginners, inside the If, a line before populating the $TempObject, I can just add this line, $orgID = $RefObj | Where-Object Name -eq $Row.Name. Then the first element in $TempObject, add the this line, ID = $orgID.ID — Joseph K. Perez, Jun 18 '20 at 23:57

score 2 · Accepted Answer · answered Jun 18 '20 at 19:22

I'm also alway struggling with Compare-Object, so I hope there is a better answer than this:

$RefObj = @'
ID,Name,Location,Gender
1,Peter,USA,Male
2,Paul,UK,Male
3,Mary,PI,Female
'@ | ConvertFrom-Csv

$DffObj = @'
Name,Location,Gender
Peter,USA,Female
Paul,UK,Male
Mary,USA,Female
Tom,PI,Female
Barry,CAN,Male
'@ | ConvertFrom-Csv

$MyCSVFields = @('Name','Location','Gender')
$CompareResults = Compare-Object $RefObj $DffObj -Property $MyCSVFields -PassThru

$NewOrChangedData = $CompareResults | Where-Object { $_.SideIndicator -eq '=>' } | ForEach-Object { 
    $name = $_.Name
    [PsCustomObject]@{
        ID = ($RefObj | Where-Object { $_.Name -eq $name }).ID
        Name = $name
        Location = $_.Location
        Gender = $_.Gender
        #Sanity check "Compare Indicator" = $_.SideIndicator 
    }
}

$NewOrChangedData

Result:

ID Name  Location Gender
-- ----  -------- ------
1  Peter USA      Female
3  Mary  USA      Female
   Tom   PI       Female
   Barry CAN      Male

Thank you. I appreciate you walking it through for the solution. I understand now how to match and reference a field from the master file using the Where-Object. Also appreciated the advanced (shortcut) notation in your example. — Joseph K. Perez, Jun 18 '20 at 23:28

PowerShell Compare-Object to produce change report file

1 Answers1