Powershell transpose rows into columns

Question

Can someone help me to transpose row into colums. Need to transpose MachineName into columns.
Endtime must be sorted.

<#MachineName, TotalDataSizeBytes, ActualStartTime, EndTime,   FinalJobStatus
SERVER1, 322349304901, 28/02/2016 23:00:03, 29/03/2016 23:33:23, OK
SERVER1, 322349304902, 26/02/2016 23:00:03, 27/03/2016 23:33:23, OK
SERVER2, 322349304903, 28/02/2016 23:00:01, 29/03/2016 23:33:23, OK
SERVER2, 322349304904, 26/02/2016 23:00:01, 27/03/2016 23:33:23, OK
#>

$graph = Import-Csv d:\report3.csv | foreach {

  New-Object PSObject -prop @{
    EndTime = [DateTime]::Parse($_.EndTime);
    MachineName = $_.MachineName;
    TotalDataSizeBytes = $_.TotalDataSizeBytes
  }
} 

#Desired result in a csv file.

#EndTime,SERVER1,SERVER2
#27/03/2016 23:33:23,322349304902,322349304904
#29/03/2016 23:33:23,322349304901,322349304903


Edit1. Found a similar question:
http://stackoverflow.com/questions/33906500/powershell-csv-row-column-transpose-and-manipulation

No. It comes from a backup report. Maybe a backup server failed that day — user2637202, Mar 04 '16 at 11:01

score 2 · Accepted Answer · answered Mar 04 '16 at 11:12

2

Start by finding all relevant machine names, we'll need those later:

$Rows = Import-Csv d:\report3.csv
$MachineNames = $Rows |Select-Object -ExpandProperty MachineName |Sort -Unique

Next up, group all entries by Endtime, and add the values from all rows that are grouped together into a single object:

$ConsolidatedRows = $Rows |Group-Object EndTime |ForEach-Object {
    $NewRowProperties = @{ EndTime = [DateTime]::Parse($_.Name) }
    foreach($Row in $_.Group)
    {
        $NewRowProperties.Add($Row.MachineName,$Row.TotalDataSizeBytes)
    }
    New-Object psobject -Property $NewRowProperties
}

And then finally, select the EndTime property and all the MachineName-specific properties using the names we grabbed earlier:

$ConsolidatedRows |Select-Object @("EndTime";$MachineNames) |Export-Csv .\finalReport.csv -NoTypeInformation

answered Mar 04 '16 at 11:12

Mathias R. Jessen

157,619
12
148
206

Thanks a lot Mathias! How can I order by DateTime (EndTime)? – user2637202 Mar 04 '16 at 11:27
Stick a `Sort-Object EndDate` statement into the pipeline, just before `Export-Csv` – Mathias R. Jessen Mar 04 '16 at 11:27
`Sort-Object EndTime` Thanks a lot, Mathias! Very jealous! – user2637202 Mar 04 '16 at 11:38
No need for jealousy. Just practice and you'll be a natural one day :-) – Mathias R. Jessen Mar 04 '16 at 12:24
Sorry @MathiasR.Jessen I am trying to add ActualStartTime in the csv file. `EndTime, ActualStartTime, SERVER1, SERVER2'. Can you help me? – user2637202 Mar 07 '16 at 09:42

Powershell transpose rows into columns

1 Answers1

Linked