none
What's Wrong WIth Export-CSV? RRS feed

  • Question

  • I am far from an expert with Powershell, so please forgive me if I'm missing something simple.

    When I run the following script in Powershell 5.0

    Get-MsolUser-All|WHERE{$_.IsLicensed -eq'TRUE'} |selectUserPrincipalName,Displayname,  LastName,FirstName,Licenses  |SORT-ObjectUserPrincipalName|Format-Table-AutoSize|Export-CsvH:\PowerShell\PowerShellResults\MSOLLicenses12Apr16.csv 

    All I get back is a file that starts out like this:

    #TYPE   Microsoft.PowerShell.Commands.Internal.Format.FormatStartData
    ClassId2e4f51ef21dd47e99d3c952918aff9cd pageHeaderEntry pageFooterEntry autosizeInfo shapeInfo groupingEntry
    033ecb2bc07a4d43b5ef94ed5a35d280 Microsoft.PowerShell.Commands.Internal.Format.AutosizeInfo Microsoft.PowerShell.Commands.Internal.Format.TableHeaderInfo
    9e210fe47d09416682b841769c78b8a3
    27c87ef9bbda4f709f6b4002fa4af63c
    27c87ef9bbda4f709f6b4002fa4af63c

    However, when I run it without the EXPORT-CSV I get the data I am expecting. 

    I have run other scripts (today) against using cmdlets  (e.g. Get-Mailbox) where EXPORT-CSV is working fine.

    Any thoughts would be greatly appreciated.

    TIA,

    John

    Tuesday, April 12, 2016 2:40 PM

Answers

All replies

  • Hi,

    If you want to export to CSV you should drop the Format-Table command.


    • Marked as answer by DevalilaJohn Tuesday, April 12, 2016 3:54 PM
    Tuesday, April 12, 2016 2:49 PM
  • Thanks Mike.  That got the data properly formatted.  It also changed the last column, Licenses, from data values to a descriptor, System.Collections.Generic.List`1[Microsoft.Online.Administration.UserLicense].  One more challenge.

    :{)

    Tuesday, April 12, 2016 3:56 PM
  • Cheers, you're welcome John.

    I don't have these cmdlets to test with, but generally you can get around that by using -join in a calculated property. Here's an example:

    Get-WmiObject Win32_ComputerSystem |
        Select Name,Roles |
            Export-Csv .\notGood.csv -NoTypeInformation
    
    
    Get-WmiObject Win32_ComputerSystem |
        Select Name,@{N='Roles';E={$_.Roles -join ', '}} |
            Export-Csv .\allGood.csv -NoTypeInformation


    Tuesday, April 12, 2016 4:08 PM
  • Thanks for another quick reply Mike.  Unfortunately, this time no joy. :{(

    I simplified the request as much as I could so now it is simply:

    Get-MsolUser-MaxResults10|selectObjectID,Licenses

    That yields the following, on screen (which is what I'm expecting):

    ObjectId   Licenses
    000245ff-9565-4b5b-9093-c372a54caf31 {integralife:ENTERPRISEWITHSCAL}
    0010e4d8-2fed-4d35-87d3-df9cf0054223 {}
    0012e713-4282-473b-977e-e19c59215eb7  {integralife:ENTERPRISEPACK} 
    001acaa7-3765-4803-a79e-e1ae24489f9e {}
    0022c924-9860-4f95-aff2-18e7774f7a24  {integralife:DESKLESSPACK}  
    002e5004-731f-4e9d-82d1-bce651215c5c  {integralife:ENTERPRISEPACK}  
    0039b60b-eb32-497f-a474-5915003be3ed  {integralife:DESKLESSPACK}
    003ec59c-a1d7-44a9-98e5-ab12cc2adcdb {}
    003f8264-8012-43e7-b84f-597cac2a70ee  {integralife:ENTERPRISEPACK} 
    0042d361-9a8a-4289-8f32-088d4dfa4491 {}

    (the empty curly braces are okay)

    When I add the EXPORT-CSV, I end up with:

    ObjectId Licenses
    000245ff-9565-4b5b-9093-c372a54caf31 System.Collections.Generic.List`1[Microsoft.Online.Administration.UserLicense]
    0012e713-4282-473b-977e-e19c59215eb7 System.Collections.Generic.List`1[Microsoft.Online.Administration.UserLicense]
    0022c924-9860-4f95-aff2-18e7774f7a24 System.Collections.Generic.List`1[Microsoft.Online.Administration.UserLicense]
    002e5004-731f-4e9d-82d1-bce651215c5c System.Collections.Generic.List`1[Microsoft.Online.Administration.UserLicense]
    0039b60b-eb32-497f-a474-5915003be3ed System.Collections.Generic.List`1[Microsoft.Online.Administration.UserLicense]
    003f8264-8012-43e7-b84f-597cac2a70ee System.Collections.Generic.List`1[Microsoft.Online.Administration.UserLicense]

    I tried using your -join suggestion (copied it into my code and replaced Roles with Licenses but it made no difference).

    What strikes me as so weird is that in other extracts (other cmdlets) I don't see this issue.  I'm wondering if it's something to do with the objects described as System.Collections.Generic.List[Microsoft.Online.Administration...]

    Best regards,

    John

    Tuesday, April 12, 2016 6:41 PM