我有两个CSV文件。 TeamCSV和TeamDetailsCSV。请参阅下面的格式。
我想通过使用TeamCSV(团队,名称,数字,颜色)作为基础并在TeamDetailsCSV(形状和代码)中进行一些查找来输出第三个CSV(下面的所需输出)。
但是,团队A和团队E组成一个阵列(请参见下面的BadOutputCSV)。我想按DesiredOutputCSV中的说明逐行导出thirdCSV。我在下面发布了我的代码。
感谢对此的任何帮助。现在已经裂了几天头。非常感谢大家。
PS C:\temp1> $TeamCSV
Team Name Number Color
---- ---- ------ -----
Team A John 1 Blue
Team A Mary 2 Blue
Team A James 5 Red
Team B Mark 5 Red
Team C Joey 3 Green
Team D Amy 3 Green
Team E Rose 4 Green
Team E Janet 2 Red
PS C:\temp1> $TeamDetailCSV
Team Shape Code
---- ----- ----
Team A Circle TC01
Team B Square TC01
Team C Triangle TC01
Team D Hexagon TC02
Team E Pentagon TC03
PS C:\temp1> $DesiredOutput | ft
Team Name Number Color Shape Code
---- ---- ------ ----- ----- ----
Team A John 1 Blue Circle TC01
Team A Mary 2 Blue Circle TC01
Team A James 5 Red Circle TC01
Team B Mark 5 Red Square TC01
Team C Joey 3 Green Triangle TC01
Team D Amy 3 Green Hexagon TC02
Team E Rose 4 Green Pentagon TC03
Team E Janet 2 Red Pentagon TC03
PS C:\temp1> $BadOutput | ft
Team Name Number Color Shape Code
---- ---- ------ ----- ----- ----
System.Object[] System.Object[] System.Object[] System.Object[] Circle TC01
Team B Mark 5 Red Square TC01
Team C Joey 3 Green Triangle TC01
Team D Amy 3 Green Hexagon TC02
System.Object[] System.Object[] System.Object[] System.Object[] Pentagon TC03
这是我的代码
$TeamCSV = Import-Csv .\Team-conv.csv
$TeamDetailCSV = Import-Csv .\TeamDetail-conv.csv
$TeaminBoth = Compare-Object -ReferenceObject $TeamCSV.Team -DifferenceObject $TeamDetailCSV.team -IncludeEqual | #Posh v3
Where-Object {$_.SideIndicator -eq "=="} |
Select-Object -ExpandProperty InputObject
$OutputCSV = ForEach($Team in $TeaminBoth) {
$columnTeamCSV = $TeaminBoth | Where-Object {$_.Team -eq $Team}
$columnTeamDetailCSV = $TeaminBoth | Where-Object {$_.Team -eq $Team}
[PSCustomObject][Ordered] @{
Team = $columnTeamCSV.Team
Name = $columnTeamCSV.Name
Number = $columnTeamCSV.Number
Color = $columnTeamCSV.Color
Shape = $columnTeamDetailCSV.Shape
Code = $columnTeamDetailCSV.Code
}
$OutputCSV | Export-CSV -NoTypeInformation -Path $xlsxPath\teamdetails.csv
最佳答案
这是使用哈希表完成此操作的一种方法:
Import-Csv .\TeamDetail.csv |
ForEach-Object {$teamLookup=@{}}{
$teamLookup[$_.Team] = $_
}
Import-Csv .\Team.csv |
ForEach-Object {
$_ | Add-Member -MemberType NoteProperty -Name Shape -Value $teamLookup[$_.Team].Shape -PassThru |
Add-Member -MemberType NoteProperty -Name Code -Value $teamLookup[$_.Team].Code -PassThru
} | Export-csv .\DesiredOutput.csv
如果导入
DesiredOutput.csv
,则会得到:Team Name Number Color Shape Code
---- ---- ------ ----- ----- ----
Team A John 1 Blue Circle TC01
Team A Mary 2 Blue Circle TC01
Team A James 5 Red Circle TC01
Team B Mark 5 Red Square TC01
Team C Joey 3 Green Triangle TC01
Team D Amy 3 Green Hexagon TC02
Team E Rose 4 Green Pentagon TC03
Team E Janet 2 Red Pentagon TC03
关于arrays - Powershell CSV嵌套哈希表/数组查找,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/49944471/