locked
Getting "System.Object[]" in a column when I try to export a variable as CSV RRS feed

  • Question

  • I found some guides but I am not able to apply them to my case. Basically, I managed to get the information from Office 365 as a variable and I would like to export certain fields as CSV to my system using powershell but getting System.Object[] in the output for a specific column when I try to export. Following is the information in table format:

    PS /root/downloads> $ServiceStatus.Value | Select-Object IncidentIDs,WorkloadDisplayName,Status

    IncidentIds WorkloadDisplayName                       Status
    ----------- -------------------                       ------
    {}          Microsoft Bookings                        ServiceOperational
    {EX208974}  Exchange Online                           ServiceDegradation
    {}          Microsoft Forms                           ServiceOperational
    {}          Microsoft Kaizala                         ServiceOperational
    {}          Skype for Business                        ServiceOperational
    {}          Microsoft Power Automate in Microsoft 365 ServiceOperational
    {}          Microsoft Teams                           ServiceOperational
    {}          Mobile Device Management for Office 365   ServiceOperational
    {}          Office Client Applications                ServiceOperational
    {}          Office for the web                        ServiceOperational
    {}          OneDrive for Business                     ServiceOperational
    {}          Identity Service                          ServiceOperational
    {}          Office 365 Portal                         ServiceOperational
    {}          Office Subscription                       ServiceOperational
    {}          Planner                                   ServiceOperational
    {}          Power Apps in Microsoft 365               ServiceOperational
    {SP208894}  SharePoint Online                         ServiceDegradation
    {}          Microsoft StaffHub                        ServiceOperational
    {}          Sway                                      ServiceOperational
    {}          Yammer Enterprise                         ServiceOperational

    And this is what I get when I try to export it to CSV:

    PS /root/downloads> $ServiceStatus.Value | Select-Object IncidentIDs,WorkloadDisplayName,Status | export-csv -Path 'services.csv'

    PS /root/downloads>
    PS /root/downloads>
    PS /root/downloads> cat ./services.csv
    "IncidentIds","WorkloadDisplayName","Status"
    "System.Object[]","Microsoft Bookings","ServiceOperational"
    "System.Object[]","Exchange Online","ServiceDegradation"
    "System.Object[]","Microsoft Forms","ServiceOperational"
    "System.Object[]","Microsoft Kaizala","ServiceOperational"
    "System.Object[]","Skype for Business","ServiceOperational"
    "System.Object[]","Microsoft Power Automate in Microsoft 365","ServiceOperational"
    "System.Object[]","Microsoft Teams","ServiceOperational"
    "System.Object[]","Mobile Device Management for Office 365","ServiceOperational"
    "System.Object[]","Office Client Applications","ServiceOperational"
    "System.Object[]","Office for the web","ServiceOperational"
    "System.Object[]","OneDrive for Business","ServiceOperational"
    "System.Object[]","Identity Service","ServiceOperational"
    "System.Object[]","Office 365 Portal","ServiceOperational"
    "System.Object[]","Office Subscription","ServiceOperational"
    "System.Object[]","Planner","ServiceOperational"
    "System.Object[]","Power Apps in Microsoft 365","ServiceOperational"
    "System.Object[]","SharePoint Online","ServiceDegradation"
    "System.Object[]","Microsoft StaffHub","ServiceOperational"
    "System.Object[]","Sway","ServiceOperational"
    "System.Object[]","Yammer Enterprise","ServiceOperational"
    PS /root/downloads>

    Some sources on the internet mentions that I need to convert the part that gives me trouble to string but I am not sure how to do that. Is there a function include in my export statement to fix the sutiation?

    Thanks

    Friday, April 10, 2020 5:03 PM

Answers

  • Something like this:

    $ServiceStatus.Value | Select-Object @{n="IncidentIDs";e={$_.IncidentIds -join ";"}},WorkloadDisplayName,Status


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    • Marked as answer by masavran Saturday, April 11, 2020 6:24 PM
    Friday, April 10, 2020 7:37 PM

All replies

  • Properties that are arrays cannot be exported directly.  Use the "-join" operator to concatenate them into a string.  This is done by using a calculated property.

    Read the following for an explanation and example of how to use a calculated property.  See the example for this method.

    https://docs.microsoft.com/en-us/powershell/module/Microsoft.PowerShell.Utility/Select-Object?view=powershell-7&viewFallbackFrom=powershell-5.0


    \_(ツ)_/

    Friday, April 10, 2020 6:04 PM
  • Thanks for the link.

    I reviewed it and tried to apply to my case but I still can't figure how to do this. Basically, what I am trying to get is something like this:

    "IncidentIds","WorkloadDisplayName","Status"
    "{}","Microsoft Bookings","ServiceOperational"
    "{EX208974}","Exchange Online","ServiceDegradation"
    "{}","Microsoft Forms","ServiceOperational"
    "{}","Microsoft Kaizala","ServiceOperational"

    Do you have any clues that you can share.

    Thanks

    Friday, April 10, 2020 6:53 PM
  • Something like this:

    $ServiceStatus.Value | Select-Object @{n="IncidentIDs";e={$_.IncidentIds -join ";"}},WorkloadDisplayName,Status


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    • Marked as answer by masavran Saturday, April 11, 2020 6:24 PM
    Friday, April 10, 2020 7:37 PM
  • Worked like a charm.. 

    Thank you very much.

    Saturday, April 11, 2020 6:24 PM
  • Or export to json instead, which supports arrays :)


    [pscustomobject]@{Incidentids=@()
      WorkloadDisplayName='Microsoft Bookings'
      Status='ServiceOperational'} | convertto-json

    {
      "Incidentids": [],
      "WorkloadDisplayName": "Microsoft Bookings",
      "Status": "ServiceOperational"
    }

    • Edited by JS2010 Sunday, April 12, 2020 5:07 PM
    Sunday, April 12, 2020 2:21 PM