none
Powershell export to CSV RRS feed

  • Question

  • Hi,

    I've been working on copying a script we've used at my workplace previously. This script through Powershell is supposed to find the computer's name, and operativesystem as well as systemversion and servicepack (if any), and have this exported to Excel. Or rather CSV as the format mostly used from what I can see.

    My issue however is that upon opening the CSV file, for each computer the info will be squashed into the first box on each line. I'd much prefer if each bit of information automaticly was assigned to each box in the file to make it automaticly more organized. Is this possible to do?

    The command I use for this result is as following:

    Get-ADComputer -Filter "Name -like 'COMPUTERNAME*'" -Property * | Select-Object Name,OperatingSystem,OperatingSystemServicePack,OperatingSystemVersion | Export-CSV All-Windows.csv -NoTypeInformation -Encoding UTF8

    Friday, October 18, 2019 12:06 PM

All replies

  • The parameter is -Properties rather than -Property. Otherwise, the resulting csv file should be comma delimited, which should open properly in Excel. I would add spaces after the commas in the Select clause, but I don't think that is the problem. And since all of the selected attributes should have values (not be missing) for all computers, there should be no need to list them in the -Properties parameter.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Friday, October 18, 2019 12:45 PM
  • Try using a semicolon as delimiter.

    Export-CSV All-Windows.csv -NoTypeInformation -Encoding UTF8 -Delimiter ";"


    Friday, October 18, 2019 12:50 PM
  • Hi Solaren.

    First of all, there's an error in that PowerShell snippet: you have to write "Properties" rather than "Property".
    What happens if you open the .CSV file using Notepad? Could you post a screenshot?

    Bye.


    Luigi Bruno
    MCP, MOS, MTA, MCTS, MCSA, MCSE

    Friday, October 18, 2019 12:59 PM
  • Hi Solaren,

    I have executed above command and it is generating CSV file correctly. Can you share screenshot of the CSV file opened in notepad.

    Friday, October 18, 2019 1:00 PM
  • First of all, there's an error in that PowerShell snippet: you have to write "Properties" rather than "Property".

    Just for the sake of correctness: the parameter -Properties has an alias -Property. So you can use both - it is not an error.  ;-)

    Live long and prosper!

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

    Friday, October 18, 2019 1:11 PM
  • Try this an notice the result:

    Get-ADComputer -Filter "Name -like 'COMPUTERNAME*'" -Properties OperatingSystem,OperatingSystemServicePack,OperatingSystemVersion | 
    	Select-Object Name,OperatingSystem,OperatingSystemServicePack,OperatingSystemVersion | 
    	Export-CSV All-Windows.csv -NoTypeInformation
    
    Import-Csv All-Windows.csv # <<<==== checks to validate the results

    Be careful about outputting non-default output encoding.


    \_(ツ)_/



    Friday, October 18, 2019 5:34 PM
    Moderator
  • * Set "Encoding" to "Default".
    * Specify "Delimitter".
    * Add '"sep =" <Delimitter> "' to the first line
    * When opening with other than Excel, it is necessary to ignore the first line.

    # CSV creation that can be read with Excel
    $Path = "All-Windows.csv"
    
    Get-ADComputer -Filter "Name -like 'COMPUTERNAME*'" -Property * `
    | Select-Object Name,OperatingSystem,OperatingSystemServicePack,OperatingSystemVersion `
    | Export-CSV All-Windows.csv -NoTypeInformation -Encoding Default -Delimiter ";"
    
    "`"sep=;`"`n" + (Get-Content $path -Encoding Default | out-string) | out-file $path
    
    # get csv
    $txt = (get-content $path -Encoding Default)
    $txt[1..($txt.count -1)] | ConvertFrom-Csv -Delimiter ";"



    Monday, October 21, 2019 1:40 AM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee

    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Friday, November 8, 2019 1:36 PM
    Moderator