Author Posts

September 13, 2013 at 4:34 am

Wondering if there is anyway, theoutput of a command can be transposed(Columns converted to Rows and vice-versa) in CSV

e.g :

PS C:\> Get-Service -Name bit*

Status Name DisplayName
—— —- ———–
Running BITS Background Intelligent Transfer Ser...

But what I want the output to be like

Status : Running
Name : BITS
DisplayName : Background Intelligent Transfer Service

Any ideas are most welcome! Thanks in advance

Cheers!
Surya

September 13, 2013 at 11:16 am

This should send you in the right direction. There are a plethora of examples using Excel.Application for formatting:

$xl = New-Object -ComObject "Excel.Application"
$xl.Visible = $true
$xl.DisplayAlerts = $false #for debugging, no prompts to save, etc.

$wkbk = $xl.Workbooks.Add()
$sheet = $wkbk.WorkSheets.Item(1)
$sheet.Name = "Transposed"

$results = Get-Service -Name *bits* | Select Name, Status, DisplayName

$column = 1
$row = 1
foreach ($psRow in $results{
     foreach ($item in $psRow.PSObject.Properties) {
        $sheet.Cells.Item($row, $column) = $item.Name
        $column++
        $sheet.Cells.Item($row, $column) = $item.Value
        $row++
        $column--
    }
}