Merge 2 CSV by Value “Name” and merge Count (Power

问题:

So at the momemt I'm searching for a way to merge 2 CSV files.

Here is an example for what I mean:

CSV1

"Name","Count"
"Klaus","3"
"Hans","2"
"Gerhard","1"
"Nina","6"
"Julia","10"
"Caro","19"

CSV2

"Name","Count"
"Klaus","2"
"Hans","1"
"Gerhard","1"
"Nina","1"

Now if I merge both, the output/result should be:

"Name","Count"
"Klaus","5"
"Hans","3"
"Gerhard","2"
"Nina","7"
"Julia","10"
"Caro","19"

I tried a lot, but I´ve never had suscess; I always had wrong results. Does anyone have an idea how to do this?

回答1:

You can use Group-Object (alias group) to group everything by the Name property. Then you just need to sum up the Count property of each guy in the group. Measure-Object (alias measure) will do sums for you.

$grouped = Import-Csv .\csv1.csv, .\csv2.csv | group Name
$combined = $grouped |%{ 
   New-Object PsObject -Prop @{ Name = $_.Name; Count = ($_.Group | measure -sum -prop Count).Sum }
}
$combined | Export-Csv .\combined.csv -NoType

回答2:

Import the CSV files and convert each to a hash table, then find the common names:

$csv1 = Import-Csv -Path csv1.csv
$csv2 = Import-Csv -Path csv2.csv

$HashCSV1 = @{}
$HashCSV2 = @{}
$HashMerge = @{}

foreach($r in $csv1)
{
    $HashCSV1[$r.Name] = $r.Count
}

foreach($r in $csv2)
{
    $HashCSV2[$r.Name] = $r.Count
}

foreach ($key in $HashCSV1.Keys) { 
    if ($HashCSV2.ContainsKey($key)) {
        $HashMerge[$key] = [int]$HashCSV1[$key] + [int]$HashCSV2[$key]
    } else {
        $HashMerge[$key] = $HashCSV1[$key]
    }
}

foreach ($key in $HashCSV2.Keys) { 
    if (-not $HashCSV1.ContainsKey($key)) {
        $HashMerge[$key] = $HashCSV2[$key]
    }
}

&{$HashMerge.getenumerator() |
  foreach {new-object psobject -Property @{Name = $_.name;Count=$_.value}}
  } | export-csv merge.csv -notype