比较2个CSV文件并写入所有差异 [英] Compare 2 CSV files and write all differences
问题描述
我有3个CSV文件,其中包含用户信息. CSV1是所有非活动用户的主"列表. CSV2是需要停用的当前用户列表,而CSV3是需要停用的用户列表.
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.
我想要的是一个PowerShell脚本,可以从另一个脚本(创建CSV2/3的脚本)中调用该脚本,以比较CSV1/2并将所有唯一记录写回到CSV1.然后,我希望它比较CSV1/3并删除CSV3中存在的CSV1中的所有记录. CSV2/3每天都会更改,除了标头外,可能没有任何数据.
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.
有几个唯一的字段,但是我想对"EmployeeID"进行比较. 所有3个CSV文件都具有标头(所有标头都具有相同的标头,因此数据是一致的).
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).
到目前为止,我将要完成的工作是将记录从CSV2添加到CSV1,但同时添加了两个标头.
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
推荐答案
我不知道确切的答案,但是您不能这样做吗?
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
如果您知道它们之间的差异,那么很容易将它们写入另一个csv中.
If you know the differences it is easy enough to write them in the other csv.
我没有比较对象的丰富经验,但是由于它是一个csv,您可以使用它删除列.
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
此命令将读取您的最后一个csv,然后选择要保留的列并将其导出到新的csv.
This command will read your last csv and select the columns you want to keep and export it to a new csv.
添加一个远程项目命令以删除不再需要的所有csv,并完成操作.
Add a remote-item command to remove any csv's you don't need anymore and your done.
这篇关于比较2个CSV文件并写入所有差异的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!