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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
$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:
I don’t know the exact answer but can’t you do something like this?
|
1 2 3 4 |
$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.
|
1 2 |
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.