Anyway to transpose excel content through powershell

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 3 years, 7 months ago.

  • Author
    Posts
  • #10107
    Profile photo of suryakanta das
    suryakanta das
    Participant

    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

  • #10117
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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--
        }
    }
    

You must be logged in to reply to this topic.