Datatransformation with PowerShell

Question

I would appreciate some help to transform some data with powershell and i have no idea where to begin.

My data csv looks like this:

System Name | Key   | Value
S1          | cpu   | Xeon ...
S1          | cores | 8
S1          | cpu   | Xeon ...
S1          | cores | 8
S1          | RAM1  | 8
S1          | RAM2  | 8
...
S2          | cpu   | Xeon ...

What i need is to create a table that looks like this:

System Name | CPUType | CPUs | Cores
S1          | Xeon .. | 2    | 16
S2          | Xeon .. | 1    | 4

What i have tried is this:

$csvPath = "C:\Users\rene\Desktop\Report_Machine_Hardware.csv"
$csv = Import-Csv $csvPath | Group-Object machineName
$hash = @{}
foreach ($obj in $csv) {

    # Werte pro Server
    $cpuCores = 0
    $cpus = 0
    $cputype = ""



    #$obj | fl

    foreach ($grp in $obj.Group)
    {

        #$grp | fl
        if ($grp.attributeName -eq "NumberOfCores")
        {
            $cpuCores = $cpuCores + [int]$grp.attributeValue
            #write-host $grp.attributeValue
        }

        if ($grp.attributeName -eq "ProcessorId")
        {
            $cpus++
        }

        if ($grp.attributeName -eq "Name")
        {
            $cputype = $grp.attributeValue
        }       


    }

    #Write-Host "Host $($grp.machineName) hat $cpus CPUs mit $cpuCores CPU Cores vom Typ $cputype"

    $out = @($grp.machineName,$cputype,$cpus,$cpuCores)
    $hash.Add($grp.machineName,$out)
}
Export-CSV C:\temp\test.csv -InputObject $hash  -NoTypeInformation 

For each system i get an array with systemname and my values but now i need to bring each single array into a list form with a headline to export it into csv again. The last 4 lines do not work as needed. Most likely there is an easy way to do it, but i think a have some kind of mental block right now. Can anybody help me please.

Regards René

Answer 1

Based on scepticalist good answer, but
I'd use a [PSCustomObject] (PSv3+) and assign output to the outer ForEach:

## Q:\Test\2018\12\11\SO_53722443.ps1
$csvPath = ".\Hardware.csv"
$csv = Import-Csv $csvPath | Group-Object SystemName

$NewOutput = ForEach ($obj in $csv) {
    $cpuCores = $cpus = $cputype = $Null
    ForEach ($grp in $obj.Group){
        if ($grp.key -eq "cores"){
            $cpuCores += [int]$grp.Value
        }
        if ($grp.key -eq "CPU") {
            $cpus++
            $cputype = $grp.Value
        }
    }
    [PSCustomObject]@{
        SystemName = $grp.SystemName
        CPUType    = $cputype
        CPUs       = $cpus
        CPUcores   = $cpuCores
    }
}
$NewOutput
$NewOutput | Export-CSV .\test.csv -NoTypeInformation

Answer 2

As above - I also think the data and your attribute names in the code are mismatched.

Data format:

Try using PS objects instead and use the attribute names from the csv:

$csvPath = "C:\temp\book1.csv"
$csv = Import-Csv $csvPath | Group-Object SystemName
ForEach ($obj in $csv) {

# Werte pro Server
$cpuCores = 0
$cpus = 0
$cputype = ""



#$obj | fl

$NewOutput = foreach ($grp in $obj.Group)
{
    if ($grp.key -eq "cores")
    {
        $cpuCores = $cpuCores + [int]$grp.Value
        #write-host $grp.attributeValue
    }

    if ($grp.key -eq "CPU")
    {
        $cpus++
        $cputype = $grp.Value
    }
}


#Write-Host "Host $($grp.machineName) hat $cpus CPUs mit $cpuCores CPU Cores vom Typ $cputype"
$data = new-object psobject
$data | add-member @{SystemName = $grp.SystemName;CPUType = $cputype;CPUs = $cpus;CPUcores =$cpuCores}
$data
}
$NewOutput | Select SystemName, CPUType, CPUs, CPUCores | Export-CSV C:\temp\test.csv -NoTypeInformation