Powershell CSV 嵌套哈希表/数组查找

Question

我有两个CSV文件。TeamCSV & TeamDetailsCSV. 请参阅下面的格式。

我想通过使用TeamCSV(Team，Name，NUmber，Color(作为基础并在TeamDetailsCSV(形状和代码(中进行一些查找来输出第三个CSV(下面的所需输出(。

但是，团队 A 和团队 E 在一个数组中组合在一起(请参阅下面的 BadOutputCSV(。我想按照DesiredOutputCSV上的描述逐行导出第三个CSV。我在下面发布了我的代码。

感谢对此的任何帮助。几天来一直在开脑袋。谢谢大家。

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 $xlsxPathteamdetails.csv

Answer 1

以下是使用哈希表执行此操作的一种方法：

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

Answer 2

这对

我有用。这使用数据表，可能不是您要查找的，但如果您需要进一步扩展它，至少应该对您有所帮助。实质上，您是在这两个表上进行联接。

这会从第一个 csv 添加项目，然后从第二个 csv 更新它们。

$TeamCSV = Import-Csv .Team-conv.csv
$TeamDetailCSV = Import-Csv .TeamDetail-conv.csv
$TeamDT = New-Object System.Data.DataTable
$TeamDT.Columns.Add("Team")
$TeamDT.Columns.Add("Name")
$TeamDT.Columns.Add("Number")
$TeamDT.Columns.Add("Color")
$TeamDT.Columns.Add("Shape")
$TeamDT.Columns.Add("Code")
foreach ($line in $TeamCSV)
{
    $TeamDT.Rows.Add($line.Team,$line.Name,$line.Number,$line.Color)
}
foreach ($line in $TeamDetailCSV)
{
    $TeamDT.Where({$_.Team -eq $line.Team}) | foreach {$_.Shape = $line.Shape;$_.Code = $line.Code}
}
$TeamDT | ft

Answer 3

>我假设$TeamDetailCSV中每个团队只有一个记录，没有重复的团队。 我还假设您只想在两个文件中都有有效的团队时才输出。

在这种情况下，我会像这样使用哈希表和新字段的几个计算属性来做到这一点：

$TeamDetailCSV = Import-Csv .TeamDetail-conv.csv
# Load the team information into a hash table for easy lookups
$TeamDetailHT = @{}
foreach ($Team in $TeamDetailCSV) {
    $TeamDetailHT.Add($Team.Name, $Team)
}
# Import the team member data, but ignore any data for team members that aren't for valid Teams
Import-Csv .Team-conv.csv |
    Where-Object { $TeamDetailHT.ContainsKey($_.Team) } |
    Select-Object -Property Team, Name, Number, Color, @{n = 'Shape'; e = {$TeamDetailHT[$_.Team].Shape}}, @{n = 'Code'; e = {$TeamDetailHT[$_.Team].Code}} |
    Export-Csv -NoTypeInformation -Path $xlsxPathteamdetails.csv