Compare 2 CSV files and write all differences

Question

I have 3 CSV files that contain user information. CSV1 is a "master" list of all inactive users. CSV2 is a current list of users that need to be deactivated and CSV3 is a list of users that need to be activated.

What I want is to have a PowerShell script that can be called from another script (the one that creates CSV2/3) to have it compare CSV1/2 and write all unique records back to CSV1. Then I want it to compare CSV1/3 and remove all records in CSV1 that exist in CSV3. CSV2/3 can change daily and it is possible to have no data in them, other than the header.

There are several unique fields, but I would want to compare on 'EmployeeID'. All 3 CSV files have headers (same headers in all of them, so the data is consistent).

What I have ended up with so far will add the records from CSV2 to CSV1, but it adds both headers.

$ICM= Import-Csv inactiveicmaster.csv -Header 'StudentDistrictID', 'StudentSiteCode', 'StudentLastName', 'StudentFirstName', 'StudentGradeLevel', 'GraduationYr', 'Masterck', 'Homeroom', 'MiddleName', 'Birthday', 'Gender', 'Email'
$IC = Import-Csv csv\inactiveic.csv -Header 'StudentDistrictID', 'StudentSiteCode', 'StudentLastName', 'StudentFirstName', 'StudentGradeLevel', 'GraduationYr', 'Masterck', 'Homeroom', 'MiddleName', 'Birthday', 'Gender', 'Email'
$DIS = Import-Csv csv\disinad.csv -Header 'StudentDistrictID', 'StudentSiteCode', 'StudentLastName', 'StudentFirstName', 'StudentGradeLevel', 'GraduationYr', 'Masterck', 'Homeroom', 'MiddleName', 'Birthday', 'Gender', 'Email'
foreach ($f in $ic) {
  $found = $false
  foreach ($g in $icm) {
    if ($g.StudentDistrictID -eq $f.StudentDistrictID) {
      $found = $true
    }
  }
  if ($found -eq $false) {
    $icm += $f
    if ($f.masterck -eq "") {
      $f.masterck = "IM"
    }
  }
}
<#
foreach ($h in $dis) {
  $found = $false
  foreach ($g in $icm) {
    if ($g.studentdistrictid -eq $h.studentdistrictid) {
      $found = $true
    }
    if ($found -ne $false) {
      #don't know what to do here to remove the duplicate
    }
  }
}
#>
$icm | select * | Export-Csv master.csv -NoTypeInformation

Answer 1

I don't know the exact answer but can't you do something like this?

$file1 = import-csv -Path "C:\temp\Test1.csv" 
$file2 = import-csv -Path "C:\temp\Test2.csv" 
Compare-Object $file1 $file2 -property MPFriendlyName

look at this link for complete example and result : Compare csv with same headers

If you know the differences it is easy enough to write them in the other csv.

Edit: I don't have much experience with compare-objects but since it is a csv you can just delete the column with this.

Import-Csv C:\fso\csv1.csv | select ColumnYouWant1,ColumnYouWant2| Export-Csv -Path c:\fso\csvResult.csv –NoTypeInformation

This command will read your last csv and select the columns you want to keep and export it to a new csv.

Add a remote-item command to remove any csv's you don't need anymore and your done.

Answer 2

Solution:

$ICM= Import-Csv InactiveICMaster.csv
$IC = Import-Csv csv\InactiveIC.csv
$DIS = Import-Csv csv\DisinAD.csv
foreach ($f in $ic)
{
    $found = $false
foreach($g in $icm)
{
    if ($g.StudentDistrictID -eq $f.StudentDistrictID) 
    {
        $found = $true
    }
}
if ($found -eq $false)
{
    $icm += $f
    if ($f.masterck -eq "")
    {
        $f.masterck = "IM"
    }

}
}
$icm | select * | export-csv InactiveICMaster.csv -NoTypeInformation
$icma = import-csv InactiveICMaster.csv
compare-object $icma $dis -property studentdistrictid -passthru|Where-Object {$_.SideIndicator -eq "<="}|select StudentDistrictID,StudentSiteCode,StudentLastName,StudentFirstName,StudentGradeLevel,GraduationYr,Masterck,Homeroom,MiddleName,Birthday,Gender,Email |export-csv inactiveicmastertest.csv -NoTypeInformation
remove-item inactiveicmaster.csv
import-csv inactiveicmastertest.csv|sort StudentDistrictID|export-csv InactiveICMaster.csv -NoTypeInformation
remove-item InactiveICMasterTest.csv

Answer 3

I know this is old but wanted to answer for others looking for this solution. I am trying to use Compare-Object myself because the two matrices but am running into a problem where if one is larger than the other it runs forever making a very larger matrix with lots of dupes.

Any who, to the above solution, you may want to consider using a break when you nest loops for this purpose. It'll allow you to compare much faster. Break will tell the 2nd for-each loop to stop and move on to the next item.

Sorry, first time posting on here. not sure how to format well and I gotta get back to action.

$ICM= Import-Csv InactiveICMaster.csv
$IC = Import-Csv csv\InactiveIC.csv
$DIS = Import-Csv csv\DisinAD.csv
foreach ($f in $ic)
  foreach($g in $icm){
    if ($g.StudentDistrictID -eq $f.StudentDistrictID){
      break
    }else{
      $icm += $f
      if ($f.masterck -eq ""){
        $f.masterck = "IM"
      }

  }
}
$icm | select * | export-csv InactiveICMaster.csv -NoTypeInformation
$icma = import-csv InactiveICMaster.csv
compare-object $icma $dis -property studentdistrictid -passthru|Where-Object {$_.SideIndicator -eq "<="}|select StudentDistrictID,StudentSiteCode,StudentLastName,StudentFirstName,StudentGradeLevel,GraduationYr,Masterck,Homeroom,MiddleName,Birthday,Gender,Email |export-csv inactiveicmastertest.csv -NoTypeInformation
remove-item inactiveicmaster.csv
import-csv inactiveicmastertest.csv|sort StudentDistrictID|export-csv InactiveICMaster.csv -NoTypeInformation
remove-item InactiveICMasterTest.csv