Getting unique values from a CSV

BPengu 0
Viewed 54

I have 2 CSV's. One is a list of accounts ($filename2), and the other is a list of accounts I don't want to be in the first list ($excludelist).

I am trying to use compare-object to just grab the unique values from the first list, however I have noticed some of the values in the second list are there as well. Can someone please take a look at this section of code and help me figure out where I went wrong?

$filename3 = "FinalDoc"
$excludelist = "ExcludeList"
$exclude = import-csv $excludelist
$include = import-csv $filename2
Compare-Object $exclude $include -property UserPrincipalName,ObjectId | where-object {$_.SideIndicator -like "=>"} | export-csv $filename3
azure-active-directory active-directory powershell
1 Answers

Your problem:

When you use Compare-Object with -Property, the elements of the two input collections are compared by all given properties.

  • As Santiago Squarzon points out, it should be sufficient to compare by either UserPrincipalName or ObjectId, as either value uniquely identifies a user.

However, presumably you also used -Property to select the output properties.

  • Indeed, by default you get [pscustomobject] instances that contain only the properties specified via -Property, alongside a .SideIndicator property indicating which collection the property-value combination is unique to.

By default, you cannot separate these aspects: any desired output properties invariably participate in the comparison, which fails if only the elements of one collection have all desired output properties, for instance.

The solution:

  • The -PassThru switch changes the output behavior to emitting the whole input objects instead of only the -Property values.

    • The objects still have a .SideIndicator property, which is added as a NoteProperty member via PowerShell's ETS (Extended Type System).

    • Note that if you request inclusion of elements that compare equal with -IncludeEqual, it is the element from the -ReferenceObject collection that is passed through for each matching pair of objects.

  • You can then perform the output property selection later, using Select-Object:

# Compare by UserPrincipalName only, but pass all original arguments
# through with -PassThru. Then select the output properties with Select-Object
Compare-Object -PassThru $exclude $include -Property UserPrincipalName | 
  Where-Object SideIndicator -eq '=>' | 
  Select-Object UserPrincipalName, ObjectId
  Export-Csv $filename3
mklement00
Related