Comparing two CSV's for similar strings

Question

I am currently trying to compare two CSV's. doc1 has 5000 strings in it, and doc2 has 100 strings in it. Every string in doc2 ends in "admin". I want to compare doc1 & doc2 and find all the strings that match up to the point it reaches "admin".

so as an example:

a string in doc1

a string in doc1 admin

it will output both of these to a new CSV

results = foreach ($OU in $OUs) { 
Get-ADGroup -SearchBase $OU -Filter * -Properties CanonicalName,Created,Description,ManagedBy,Member,MemberOf,Members,ObjectGUID,whenChanged | 
? {($_.ManagedBy -like $null) -and ($_.Description -notlike "*owner*") -and ($_.CanonicalName -notlike "*admin")}
}
$results | select CanonicalName,Description,ManagedBy,Member,MemberOf,Members,ObjectGUID,Created,whenChanged |
Export-Csv .\doc1.csv -NoTypeInformation

$results0 = foreach ($OU in $OUs) { 
Get-ADGroup -SearchBase $OU -Filter * -Properties CanonicalName,Created,Description,ManagedBy,Member,MemberOf,Members,ObjectGUID,whenChanged | 
? {($_.ManagedBy -like $null) -and ($_.Description -notlike "*owner*") -and ($_.CanonicalName -like "*admin")}
}
$results0 | select CanonicalName,Description,ManagedBy,Member,MemberOf,Members,ObjectGUID,Created,whenChanged |
Export-Csv .\doc2.csv -NoTypeInformation

$csvPath1 = ".\doc1.csv"
$csvPath2 = '.\doc2.csv'
$propertyToCompare = 'CanonicalName'
$csv1 = Import-Csv -Path $csvPath1
$csv2 = Import-Csv -Path $csvPath2
$duplicates = Compare-Object $csv1 $csv2 -Property $propertyToCompare -ExcludeDifferent -PassThru | Select-Object -ExpandProperty $propertyToCompare
$csv1 | Where-Object { $_.$propertyToCompare -in $duplicates } | Export-Csv -Path .\AdminsAndNotAdminsInOneFile.csv -NoTypeInformation

With Compare-Object I don't know how to make it ignore the last few characters in the string in doc2. Is there some way for me to modify the string?

I don't know what field has the requirement, but you can do `($string -replace 'admin$').Trim()` — AdminOfThings, Jan 16 '20 at 18:53

mklement0 · Accepted Answer · 2020-01-16T21:16:06.300

You can take advantage of the fact that Compare-Object supports calculated properties as comparison properties (here, only a script block ({...}) is passed, which is the same as passing
@{ Expression = { ... } }):

Compare-Object $csv1 $csv2 -ExcludeDifferent -IncludeEqual -PassThru -Property {
    $_.$propertyToCompare -replace ' admin$'
  } | Select-Object -ExpandProperty $propertyToCompare

Note that -PassThru ensures that the input objects are passed through, which in the case of objects that compare as equal means that the LHS ($csv1) object is passed through.

Expression $_.$propertyToCompare -replace ' admin$', which is evaluated for each input object, uses regex admin$ to replace ' admin' at the end ($) of the value of property $propertyToCompare; if that string isn't present, the value is used as-is.

In effect, the objects are compared as if the trailing ' admin' weren't present.

Comparing two CSV's for similar strings

1 Answers1