代码之家  ›  专栏  ›  技术社区  ›  John Reyes

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

  •  1
  • John Reyes  · 技术社区  · 7 年前

    我有两个CSV文件。团队CSV&TeamDetailsCSV。请参见下面的格式。

    我想通过使用TeamCSV(团队、名称、编号、颜色)作为基础并在TeamDetailsCSV(形状和代码)中进行一些查找来输出第三个CSV(下面需要的输出)。

    然而,团队A和团队E是在一个数组中组合在一起的(请参见下面的BadOutputCSV)。我想按照DesiredOutputCSV上的描述逐行导出第三个DCSV。我把代码贴在下面。

    感谢您在这方面的帮助。这几天我一直在头痛。非常感谢大家。

    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
    
    3 回复  |  直到 7 年前
        1
  •  2
  •   boxdog    7 年前

    使用哈希表有一种方法:

    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
    
        2
  •  1
  •   langstrom    7 年前

    这对我有用。这使用了一个datatable,可能不是您想要的,但如果您需要进一步扩展它,至少应该对您有所帮助。实际上,您正在对这两个表进行连接。

    这将从第一个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
    
        3
  •  1
  •   Bacon Bits    7 年前

    我假设每个队在 $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 $xlsxPath\teamdetails.csv