Transpose rows into columns using PowerShell

Question

I having a hard time,again, trying to transpose row into columns.

The original code comes from @Mathias R. Jessen, Powershell transpose rows into columns but now I need to add some additionals columns in the csv file. I would like to have (I don't mind about the order)

EndTime,ActualStartTime,"MachineName1", "MachineName2", MachineNameX",ElapsedTime,FinalJobStatus

 #1.csv
 #MachineName, TotalDataSizeBytes, ActualStartTime,EndTime,ElapsedTime,FinalJobStatus
 #SERVER1, 322349304901, 28/02/2016 23:00:03, 29/03/2016 23:33:23, 222,OK
 #SERVER1, 322349304902, 26/02/2016 23:00:03, 27/03/2016 23:33:24, 222,OK
 #SERVER2, 322349304903, 28/02/2016 23:00:01, 29/03/2016 23:33:25, 11, OK
 #SERVER2, 322349304904, 26/02/2016 23:00:01, 27/03/2016 23:33:26, 122,OK
 #

 #2.csv (Desired output)
 #EndTime","ActualStartTime","SERVER1","SERVER2",ElapsedTime,FinalJobStatus
 #"29/03/2016 23:33:23","28/02/2016 23:00:03","322349304901",222,OK
 #"27/03/2016 23:33:24","26/02/2016 23:00:03","322349304902",222,OK
 #"29/03/2016 23:33:25","28/02/2016 23:00:01",,"322349304903",11,OK
 #"27/03/2016 23:33:26","26/02/2016 23:00:01",,"322349304904",122,OK


$Rows = Import-Csv c:\_Scripts\1.csv
$MachineNames = $Rows |Select-Object -ExpandProperty MachineName |Sort -Unique

$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
} 
$ConsolidatedRows |Select-Object @("EndTime";$MachineNames) |
 Sort-Object EndTime |Export-Csv c:\_Scripts\2.csv -NoTypeInformation

Edit1. I have succeeded:

score 0 · Answer 1 · answered Mar 08 '16 at 14:36

$Rows = Import-Csv c:\_Scripts\1.csv
$MachineNames = $Rows |Select-Object -ExpandProperty MachineName |Sort -Unique
$ConsolidatedRows =""
$NewRowProperties =""

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

}

$ConsolidatedRows |Ft
$ConsolidatedRows |Select-Object @("EndTime";$MachineNames;"ActualStartTime";"ElapsedTime";"FinalJobStatus") 
|Sort-Object EndTime |Export-Csv c:\_Scripts\2.csv -NoTypeInformation

Transpose rows into columns using PowerShell

1 Answers1