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

Question

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.

J

Answer 1

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