PowerShell: Compare 2 columns in same csv-CodePudding

I have seen several examples of using PowerShell to compare columns in 2 separate csv files, but is there a way to compare 2 columns in the same csv, looping through each record to find the matching records? The result would be to create a new column outputting the matching value. If no match, then the output in the new column would be nothing or zero. Thank you.

CodePudding user response：

Use Select-Object with a calculated property (substitute the column names of interest for Col1 and Col2, and optionally choose a different new column name instead of Match; pipe to Export-Csv to re-export back to a CSV file):

If the column values to be compared are on the same row:

Import-Csv in.csv | 
  Select-Object *, 
            @{ Name='Match'; Expression={ if ($_.Col1 -eq $_.Col2) { $_.Col1 } } }

If the column values should be compared across all rows:

# Load all rows as objects into memory.
$allRows = Import-Csv in.csv

# Create a hash set of all distinct Col1 values.
$col1LookupValues = [System.Collections.Generic.HashSet[string]]::new(
  [string[]] $allRows.Col1,
  [StringComparer]::CurrentCultureIgnoreCase
)

$allRows |
  Select-Object *, 
    @{ Name='Match'; Expression={ 
       if ($col1LookupValues.Contains($_.Col2)) { $_.Col2 } 
     } }

Note: The use of a [System.Collections.Generic.HashSet[string]] instance makes the lookups efficient.

If performance isn't a concern and the number of rows is small enough, you could get away with
$col1LookupValues = [string[]] $allRows.Col1 and
if ($col1LookupValues -contains $_.Col2)