in PowerShell how do I Loop through CSV file and create a summary hashtable based on key value in cs-CodePudding

firstly, thanks for any help in advance, and secondly I'm new to PowerShell, so am playing around with it, and probably off on the wrong track ;)

I have a CSV file that i am reading the contents of, which is all good, and when i read the CSV i get an array. The CSV file contains something like the following information:

Name	Value1	Value2
IT	444	32
HR	34	21
IT	31	5
IT	75	3
HR	64	2

What I'm trying to achieve is to add the value based on grouping the name - so i will end up with: IT: 550,40 and HR: 98,23

I have tried using array lists and hastables and ForEach-Object, but I'm afraid that i am not getting very far. I have been using Get-Unique after converting the array to an arraylist, however i am stuck on the best approach to firstly lookup and update based on the name property, and then as to whether i should have an array of hashtables to hold the multiple items of data.

I'd appreciate any pointers, and thanks again.

CodePudding user response：

Using Group-Object, as suggested by commenter, is a good approach. Use Measure-Object to calculate the sum of the given property.

# Create sample data
$data = @'
Name,Value1,Value2
IT,444,32
HR,34,21
IT,31,5
IT,75,3
HR,64,2
'@ | ConvertFrom-Csv

# Group sample data by value of Name property
$data | Group-Object Name | ForEach-Object {

    # Create an output object for the current group
    [pscustomobject]@{
        Name   = $_.Name
        Value1 = ($_.Group | Measure-Object -Property Value1 -Sum).Sum
        Value2 = ($_.Group | Measure-Object -Property Value2 -Sum).Sum
    }
}

Though when you start with an actual CSV file, you don't need to load the whole CSV into a variable, wasting memory. Instead use the pipeline like this:

Import-Csv data.csv | Group-Object Name | ForEach-Object { ... }

Output:

Name Value1 Value2
---- ------ ------
IT      550     40
HR       98     23

CodePudding user response：

I modified a script I did yesterday

$input = @"
Name, Value1, Value2
IT,444,32
HR,34,21
IT,31,21
IT, 31,5
HR,64,2
"@

$table = $input | ConvertFrom-Csv
$table | Format-Table

$groups = $table | Group-Object {$_.Name}

$outputTable = [System.Collections.ArrayList]::new()
foreach($group in $groups)
{
$group | Format-Table
   $newRow = New-Object -TypeName psobject
   $newRow | Add-Member -NotePropertyName Name -NotePropertyValue $group.Name

   $sum = 0
   $group.Group | foreach{$sum  = $_.Value1}
   $newRow | Add-Member -NotePropertyName Value1 -NotePropertyValue $sum

   $sum = 0
   $group.Group | foreach{$sum  = $_.Value2}
   $newRow | Add-Member -NotePropertyName Value2 -NotePropertyValue $sum

   $outputTable.Add($newRow)  | Out-Null
}
$outputTable | Format-Table