locked
How to handle multi-line when exporting the data to excel in powershell RRS feed

  • Question

  • Hi all,

    I have been trying to take the hardware info for multiple remote servers and exporting them to an excel using powershell.

    Unfortunately when I try to take the processor info, for certain servers, I get 2 values ( which is actually a duplicated one)

    $file = get-Content C:\temp\VBS\Servername.txt 
     
    foreach ( $args in $file) 

    Get-WmiObject win32_processor -Credential $cre -ComputerName $args |
    ft Name
    }

    Output :

    Name                                                                                                                                                                                   
    ----                                                                                                                                                                                   
    Intel(R) Xeon(R) CPU E5-2643 0 @ 3.30GHz                                                                                                                                               
    Intel(R) Xeon(R) CPU E5-2643 0 @ 3.30GHz

    As a result of getting 2 rows, while exporting to excel using powershell, it leaves the row blank.

    At first instance, I am not sure why it returns 2 rows as output for one server. 

    Can you also let me know how to fix this multiline problem while exporting to excel please.

    Thanks,

    Sivaraj

    Friday, May 12, 2017 1:11 PM

Answers

  • Remember that Format-Table (or Format-List) doesn't work well with exporting to anywhere beyond the console window.

    $file = get-Content C:\temp\VBS\Servername.txt 
     
    foreach ( $args in $file) 
    { 
    Get-WmiObject win32_processor -Credential $cre -ComputerName $args | Select-Object -unique | Export...
    }


    • Proposed as answer by Paweł Balczunas Friday, May 12, 2017 1:15 PM
    • Marked as answer by Sivaraj.t Friday, May 12, 2017 2:58 PM
    Friday, May 12, 2017 1:15 PM

All replies

  • Remember that Format-Table (or Format-List) doesn't work well with exporting to anywhere beyond the console window.

    $file = get-Content C:\temp\VBS\Servername.txt 
     
    foreach ( $args in $file) 
    { 
    Get-WmiObject win32_processor -Credential $cre -ComputerName $args | Select-Object -unique | Export...
    }


    • Proposed as answer by Paweł Balczunas Friday, May 12, 2017 1:15 PM
    • Marked as answer by Sivaraj.t Friday, May 12, 2017 2:58 PM
    Friday, May 12, 2017 1:15 PM
  • Are you shure it's not a multi processor machine? You should try to get the complete output without to limit it to the names. You might see then DeviceID CPU0 and CPU1.

    You could this for the particular server:

    Get-CimInstance -ClassName CIM_Processor -ComputerName "ParticularServer" | Select-Object -Property DeviceID,Name,SocketDesignation


    Grüße - Best regards

    PS:> (79,108,97,102|%{[char]$_})-join''


    • Edited by BOfH-666 Friday, May 12, 2017 1:28 PM
    Friday, May 12, 2017 1:17 PM
  • Thanks very much mate! Added 'Select-Object -unique' and that helped.

    The duplicate has been removed and I can see data in excel now.

    Friday, May 12, 2017 3:00 PM
  • Yea it is not a multiprocessor machine mate. Anyways the other solution helps for now. Thanks for the idea.
    Friday, May 12, 2017 3:05 PM