Powershell import multiple csv and group by name and total up wins-CodePudding

I am trying to import multiple csv files and output a total score, i don't want to create another csv for the output, below is how the csv is stored. below is csv 1

and this is csv 2

i want to group by Name and total the wins, please see code below that i have tried

get-item -Path "File Path"   | 
ForEach-Object {
import-csv $_| 
Group-Object Name
Select-Object Name, @{ n='Wins'; e={ ($_.Group | Measure-Object Wins -Sum).Sum } } 
        }

i was hoping for an outcome like below

any help would be awesome

for some reason the current code is showing the below

Its looking better but still not grouping on Name

CodePudding user response：

This will give you the output you are expecting, with the names and total wins for each player.

$csv1 = import-csv "File path of CSV 1"
$csv2 = import-csv "File path of CSV 2"
$allRecords = $csv1   $csv2
$allRecords | Group-Object Name | Select-Object Name, @{ n='Wins'; e={ ($_.Group | Measure-Object Wins -Sum).Sum } }

the ouptut

Update

With multiple Csv Files

$allRecords = @()
$directory = "Path of the directory containing the CSV files"
$filePaths = Get-ChildItem -Path $directory -Filter "*.csv"

foreach ($filePath in $filePaths) {
    $csv = import-csv $filePath
    $allRecords  = $csv
}

$allRecords | Group-Object Name | Select-Object Name, @{ n='Wins'; e={ ($_.Group | Measure-Object Wins -Sum).Sum } }

CodePudding user response：

If you have a very high number of csv files, you'll find something like this much faster:

$CombinedRecords = Get-ChildItem -Filter *.csv -Path C:\temp | Select-Object -ExpandProperty FullName | Import-Csv 
$CombinedRecords | Group-Object Name | Select-Object Name, @{ n='Wins'; e={ ($_.Group | Measure-Object Wins -Sum).Sum } }

It can even be a one-liner:

Get-ChildItem -Filter *.csv -Path C:\temp | Select-Object -ExpandProperty FullName | Import-Csv | Group-Object Name | Select-Object Name, @{ n='Wins'; e={ ($_.Group | Measure-Object Wins -Sum).Sum } }