我正在尝试比较 2 个 XLXS,并且只想输出差异。这可以用 Compare-Object 来完成吗?
我目前正在使用这个有效的脚本,但它没有捕获我真正需要的内容:
#IMPORT PS EXCEL MODULE
import-module psexcel
#FILE TO BE COMPARED VARIABLES
$file1 = Import-XLSX C:\CurrentFile.xlsx
$file2 = Import-XLSX C:\PreviousFile.xlsx
#FILTER TO GET DATA ONLY FROM THE REFERENCE FILE ONLY
filter leftside{
param(
[Parameter(Position=0, Mandatory=$true,ValueFromPipeline = $true)]
[ValidateNotNullOrEmpty()]
[PSCustomObject]
$obj
)
$obj|?{$_.sideindicator -eq '<='}
}
#PROPERTY VARIABLE
$property = "Name" , "UserPrincipalName" , "NickName" , "Age" , "Sex"
#COMPARISON PROPER
$Comparison = Compare-Object -referenceobject $file1 -differenceobject $file2 -SyncWindow ($file1.Length / 2) -property $property | leftside
示例文件如下所示:
PreviousFile:
Name UserPrincipalName NickName Age Sex
UserUser 1 [email protected] /cdn-cgi/l/email-protection User1 20 M
UserUser 2 [email protected] /cdn-cgi/l/email-protection User2 20 M
UserUser 3 [email protected] /cdn-cgi/l/email-protection User3 20 M
UserUser 4 [email protected] /cdn-cgi/l/email-protection User90 20 M
UserUser 5 [email protected] /cdn-cgi/l/email-protection User91 20 M
CurrentFile:
Name UserPrincipalName NickName Age Sex
UserUser 1 [email protected] /cdn-cgi/l/email-protection User1 20 M
UserUser 2 [email protected] /cdn-cgi/l/email-protection User2 20 M
UserUser 3 [email protected] /cdn-cgi/l/email-protection User3 20 M
UserUser 4 [email protected] /cdn-cgi/l/email-protection User4 20 M
UserUser 5 [email protected] /cdn-cgi/l/email-protection User5 20 M
差异:UserPrincipalName 下的所有项目,除了[电子邮件受保护] /cdn-cgi/l/email-protection
昵称 User90-User4 、 User91-User5
使用当前脚本输出:
输出显示存在差异的行的完整信息。
Name UserPrincipalName NickName Age Sex
UserUser 2 [email protected] /cdn-cgi/l/email-protection User2 20 M
UserUser 3 [email protected] /cdn-cgi/l/email-protection User3 20 M
UserUser 4 [email protected] /cdn-cgi/l/email-protection User4 20 M
UserUser 5 [email protected] /cdn-cgi/l/email-protection User5 20 M
UserUser 6 [email protected] /cdn-cgi/l/email-protection User6 20 M
预期输出:
Name UserPrincipalName NickName Age Sex
UserUser 1
UserUser 2 [email protected] /cdn-cgi/l/email-protection
UserUser 3 [email protected] /cdn-cgi/l/email-protection
UserUser 4 [email protected] /cdn-cgi/l/email-protection
UserUser 5 [email protected] /cdn-cgi/l/email-protection User5
UserUser 6 [email protected] /cdn-cgi/l/email-protection User6
我希望脚本始终输出我的标识符,在本例中为“名称”,我也希望它仅输出差异。如果在旧文件和当前文件之间发现任何更改,则仅显示发现差异的当前文件。
有没有办法可以这样输出?
任何帮助,任何协助,将不胜感激。谢谢你!
经过进一步分析。我的例子可能搞砸了。
确实,我每次都需要输出我的标识符。但我需要它在有差异的条目上输出only即使我的标识符没有改变。
这是一个新的例子:
PreviousFile:
Name UserPrincipalName NickName Age Sex
UserUser 1 [email protected] /cdn-cgi/l/email-protection User1 20 M
UserUser 2 [email protected] /cdn-cgi/l/email-protection User2 20 F
UserUser 3 [email protected] /cdn-cgi/l/email-protection UserC 20 M
UserUser 4 [email protected] /cdn-cgi/l/email-protection User4 20 M
UserUser 5 [email protected] /cdn-cgi/l/email-protection User5 20 M
CurrentFile:
Name UserPrincipalName NickName Age Sex
UserUser 1 [email protected] /cdn-cgi/l/email-protection User1 20 M
UserUser 2 [email protected] /cdn-cgi/l/email-protection User2 20 M
UserUser 3 [email protected] /cdn-cgi/l/email-protection User3 20 M
UserUser 4 [email protected] /cdn-cgi/l/email-protection User4 20 M
UserUser 5 [email protected] /cdn-cgi/l/email-protection User5 20 M
UserUser 6 [email protected] /cdn-cgi/l/email-protection User6 20 M
Expected output:
Name UserPrincipalName NickName Age Sex
UserUser 2 F
UserUser 3 UserC
UserUser 4 [email protected] /cdn-cgi/l/email-protection
UserUser 5 [email protected] /cdn-cgi/l/email-protection
UserUser 6 [email protected] /cdn-cgi/l/email-protection User6 20 M
UserUser1 根本没有条目,因为一切都匹配
UserUser2已出现在列表中,因为性别更改为F
UserUser3,昵称为 UserC
UserUser4 和 5 的用户主体名称已更新为 @user.com
Useruser6 是一个新对象
所有有任何差异的条目将仅显示标识符“名称”和差异。
我尝试了一下你的代码,但我无法得到这个输出。老实说,我还不明白你剧本中发生的一切。
我希望你也能帮我解决这个问题。
谢谢你,祝你有美好的一天!