如何比较两个不同的.CSV文件中的值？

发布于 2025-01-22 20:34:41 字数 2004 浏览 0 评论 0原文

我有两个.CSV文件，一个是来自第三方应用程序的转储，另一个是Active Directory（使用PowerShell）的转储。

第三方.CSV看起来像该

电子邮件地址	dept	名称	<
a href =“/cdn-cgi/l/email-protection” class =“ __ cf_email__” data-cfemail =“ 670D080F0927100810081008150C490C49040404080A”	约翰	工程师	办公室

.csv看起来像此

电子邮件地址	名称	标题	dept
	John	Engineer	主
	办公室

方式（理想情况下是PowerShell），可以读取两个.CSV文件，并且差异突出显示并导出到第三文件。

例如，对于包含包含

像这样 - 在其中将“请更新”输入到所需的单元格中。

电子邮件	名称	标题	dept
	地址		请
	更新		工厂

菲利普

原文

I have two .csv files, one is a dump from a third party application the other is a dump from Active Directory (using PowerShell).

The third party .csv looks something like this

e-mail address	Name	Title	Dept
[email protected]	John	Engineer	Office
[email protected]	Mary	Supervisor	Factory

The AD .csv looks something like this

e-mail address	Name	Title	Dept
[email protected]	John	Engineer	Main Office
[email protected]	Mary	Team Supervisor	Factory

Is there a way (ideally in PowerShell) that the two .csv files can be read and the differences highlighted and exported to third file.

e.g for the row containing [email protected] the Dept value is different, for the row containing [email protected] the Title is different

The output would look something like this - where "Please update" has been entered into the required cell.

e-mail address	Name	Title	Dept
[email protected]	John	Engineer	Please update
[email protected]	Mary	Please update	Factory

Regards
Philip

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

蹲在坟头点根烟 2025-01-29 20:34:41

这是您可以使用group-object的一种方法，使用这些CSV作为示例：

$csv1 = @'
e-mail address,Name,Title,Dept
[email protected],John,Engineer,Office
[email protected],Mary,Supervisor,Factory
[email protected],Guy,Supervisor,Factory
'@ | ConvertFrom-Csv

$csv2 = @'
e-mail address,Name,Title,Dept
[email protected],John,Engineer,Main Office
[email protected],Mary,Team Supervisor,Factory
[email protected],Other Guy,Team Supervisor,Factory
'@ | ConvertFrom-Csv

将两个对象通过其电子邮件地址对属性进行分组IS 等于1 ，跳过该对象，因为没有什么可比较的，否则将每个属性相互比较并更新其中一个对象的属性值（索引> 0 0 <的对象/code>在这种情况下，这将是我们返回的）：

$csv1 + $csv2 | Group-Object 'e-mail address' | ForEach-Object {
    if($_.Count -eq 1) { return }
    # following assumes there will be no more than 2 elements!
    # it also assumes both CSVs have the same column names!
    foreach($prop in $_.Group[0].PSObject.Properties.Name) {
        if($_.Group[0].$prop -ne $_.Group[1].$prop) {
            $_.Group[0].$prop = 'Please Update'
        }
    }
    $_.Group[0]
} | Format-Table

从上面的示例中可以期望的结果，如您所见， guy and 其他guy 考虑比较：

e-mail address Name Title         Dept
-------------- ---- -----         ----
[email protected]  John Engineer      Please Update
[email protected]  Mary Please Update Factory

Here is one way you can do it, using Group-Object, using these CSVs as example:

$csv1 = @'
e-mail address,Name,Title,Dept
[email protected],John,Engineer,Office
[email protected],Mary,Supervisor,Factory
[email protected],Guy,Supervisor,Factory
'@ | ConvertFrom-Csv

$csv2 = @'
e-mail address,Name,Title,Dept
[email protected],John,Engineer,Main Office
[email protected],Mary,Team Supervisor,Factory
[email protected],Other Guy,Team Supervisor,Factory
'@ | ConvertFrom-Csv

Group both objects by their e-mail address property and then if the group count is equal to 1, skip that object since there is nothing to compare, else, compare each property against each other and update the property value of one of the objects (the object with index 0 in this case, which will be the one we return):

$csv1 + $csv2 | Group-Object 'e-mail address' | ForEach-Object {
    if($_.Count -eq 1) { return }
    # following assumes there will be no more than 2 elements!
    # it also assumes both CSVs have the same column names!
    foreach($prop in $_.Group[0].PSObject.Properties.Name) {
        if($_.Group[0].$prop -ne $_.Group[1].$prop) {
            $_.Group[0].$prop = 'Please Update'
        }
    }
    $_.Group[0]
} | Format-Table

The result we can expect from above example, as you can see, guy and otherguy are not taken into consideration for the comparison:

e-mail address Name Title         Dept
-------------- ---- -----         ----
[email protected]  John Engineer      Please Update
[email protected]  Mary Please Update Factory

回复收藏 0 原文

~没有更多了~