PowerShell - Compare two CSVs and use SideIndicators to export the differences to separate files

Question

I'm needing a little help creating a PowerShell script that will compare two csv files, and identify the differences specific to each file. This will be used for Active Directory user management, and will either create or disable accounts based on which csv the users are in.

I need to use the Student ID column header for the comparison since that is the only common and unique attribute shared on the accounts.

StudentRoster.csv (reference file) - I need to see a list/export of Students from this file that are not in the ActiveDirectory.csv. I will use this list to create those users in AD.

ActiveDirectory.csv - I need to see a list/export of Students in this file that are not in the StudentRoster.csv. Users on this list will be disabled in AD.

Headers/Columns: Student ID, Last Name, First Name, Grade

I'm using the below code, but I'm not sure how to tell it to flag the user as either create/disable using the SideIndicator, and how to export to the create or disable csv.

Compare-Object -ReferenceObject $(gc C:\users\Me\desktop\Test\StudentRoster.csv) -DifferenceObject $(gc C:\users\Me\desktop\Test\ActiveDirectory.csv) | Export-Csv C:\users\Me\desktop\test\export.csv

Do you care only about the existence of the Student ID in each file? What if the ID exists in both, but the other columns have differences? — Ryan Bolger
Hi Ryan, the only time a Student ID will exist in both files but with different column data is if a student has a name change. It happens, but not very often. When it does happen we remediate manually because a new email address is generated in google and their docs have to be migrated to it. In that instance, I'd still like to have the StudentRoster user flagged to be created in AD. The Student ID is the main focus for identification, but I still need the last name, first name, grade from the same row. — John

Andrew Ryan Davis Andrew Ryan Davis · Accepted Answer · 2020-09-09T19:48:43

$referenceObject = Import-Csv C:\users\Me\desktop\Test\StudentRoster.csv
$differenceObject = Import-Csv C:\users\Me\desktop\Test\ActiveDirectory.csv
$compareParams = @{
    ReferenceObject = $referenceObject
    DifferenceObject = $differenceObject
    Property = 'Student ID'
    PassThru = $true
}
$comparison = Compare-Object @compareParams | Where-Object {$psitem.SideIndicator -eq '<='} 
$comparison | Export-Csv C:\users\ardavis\desktop\export.csv -NoTypeInformation

You'll want to Import-Csv to an object with properties as headers, then Compare-Object on the property "Student ID" and PassThru to get all fields. We filter with Where-Object to choose the items existing only in the left side of the comparison, which in this case is "StudentRoster.csv".

PowerShell - Compare two CSVs and use SideIndicators to export the differences to separate files

1 Answers