Avoiding System.Object[] (or Similar Output) when using Export-Csv

I’ve ran into this issue a number of times and have seen others as well when they attempt to pipe some data which may have a collection of items in one of its properties to a CSV file using Export-Csv. What happens is something which can drive you batty. Such as the image below.

    First = 'Boe'
    Last = 'Prox'
    ExtraInfo = @(1,3,5,6)
    State = 'NE'
} | Export-Csv -notype Random.csv


As you can see, the ExtraInfo column has System.Object[] (or a different object type) instead of the 1,3,5,6. This can be frustrating to look at, especially when you have hundreds or thousands of rows of data which may have multiple columns that contains this type of information. Why does this happen? Well, it is because that anything which goes through to Export-Csv is casted as a string before being written, as in this example.



There are a few ways that you can resolve this so that the collection is unrolled (or expanded if you will) that requires a little bit of extra code, but will help to make sure that you are getting human readable information in the spreadsheet.

Using –Join

One approach to this is to use the –Join operator on those properties which will have a collection of items in it.

    First = 'Boe'
    Last = 'Prox'
    ExtraInfo = (@(1,3,5,6) -join ',')
    State = 'NE'
} | Export-Csv -notype Random.csv


Looks nice and is presentable to a person looking at the spreadsheet. Depending on the information, this may be the way for you. I’ve had data which may have 20 items in the collection and can cause that cell to become very long and if there are other various punctuations (such as working with IP addresses), then it could be harder to read.

    First = 'Boe'
    Last = 'Prox'
    ExtraInfo = (@(1,3,5,6) -join ',')
    State = 'NE'
    IPs = (@('','','','','') -join ',')
} | Export-Csv -notype Random.csv


I don’t know about you, but even if there was a space after the comma, it would still be painful to read. Because of that, I prefer to take the following approach with adjusting the output of the collection object.

Out-String and Trim()

My favorite approach (which requires a little more code and a little extra work at the end of it) to display the expanded collection in a spreadsheet by using a combination of Out-String and Trim().

    First = 'Boe'
    Last = 'Prox'
    ExtraInfo = (@(1,3,5,6) | Out-String).Trim()
    State = 'NE'
    IPs = (@('','','','','') | Out-String).Trim()
} | Export-Csv -notype Random.csv


Ok, first off you might be wondering where the rest of the data is at. Here is the part where you have to do a little formatting on the spreadsheet to get all of the data to show up. I typically will click on the upper left hand corner to select everything and then just double click on the row to expand all of the cells and then double click the columns to make sure it all looks good. I also make sure to set the vertical alignment to top as well.


After that, I then have this to view:


Now the IP Addresses and also the ExtraInfo show up as they normally would if we expanded it in the console. To me, and this is my own personal opinion, I prefer this much more than the other method. When I prepare my reports, I will typically use the ‘Format as table’ button in Excel to give it a little more color and then I ship it off to whoever needs it.


So there you go! These are just a couple of available options (I have no doubt that there are others) that you can use to make sure that your report is presentable to whoever needs to see it! As always, I am interested into seeing what others have done to get around this hurdle with sending objects with collections as properties to a spreadsheet.

A function to make things easier

I put together a function called Convert-OutputForCsv which serves as a middle man between the query for data and the exporting of that data to a CSV file using Export-Csv.

The function accepts input via the pipeline (recommended approach) and allows you to determine if you want the property to have the collection expanded to a comma separated value (comma) or if you want the stacked version that I showed above (stack). By default, the data being passed from this function to Export-Csv will not retain its order of properties (I am working on finding a solution to this) but you do have the option of defining the order manually which can be passed into the function.

Updated 02 FEB 2014: Removed OutputOrder parameter as it is no longer needed for this function. Bug has been fixed where output order didn’t match the order of the input object.

After dot sourcing the script file (. .\Convert-OutputForCsv.ps1) and loading the function into the current session, I will now demonstrate and example of how this works.

The following example will gather information about the network adapter and display its properties first without the use of the function and then using the function.

$Output = 'PSComputername','IPAddress', 'IPSubnet',

Get-WMIObject -Class Win32_NetworkAdapterConfiguration -Filter "IPEnabled='True'" |
Select-Object $Output | Export-Csv -NoTypeInformation -Path NIC.csv 



Pretty much useless at this point. Now lets run it and throw my function into the middle.

$Output = 'PSComputername','IPAddress', 'IPSubnet', 'DefaultIPGateway','DNSServerSearchOrder'

Get-WMIObject -Class Win32_NetworkAdapterConfiguration -Filter "IPEnabled='True'" |
Select-Object $Output | Convert-OutputForCSV -OutputOrder $Output | 
Export-Csv -NoTypeInformation -Path NIC.csv   


That looks a whole lot better! And just for another example, let’s see this using the comma format as well.

$Output = 'PSComputername','IPAddress', 'IPSubnet', 'DefaultIPGateway','DNSServerSearchOrder'

Get-WMIObject -Class Win32_NetworkAdapterConfiguration -Filter "IPEnabled='True'" |
Select-Object $Output | Convert-OutputForCSV -OutputOrder $Output -OutputPropertyType Comma | 
Export-Csv -NoTypeInformation -Path NIC.csv   



One more, this time with Get-ACL

$Output = 'Path','Owner', 'Access'

Get-ACL .\.gitconfig | Select-Object Path, Owner, Access, SDDL, Group| 
Convert-OutputForCSV -OutputOrder Path,Owner,Access |
Export-Csv -NoTypeInformation -Path ACL.csv



Works like a champ! Anything that I didn’t specify in the OutputOrder will just get tossed in at the end in no particular order.

The download for this function is below. Give it a spin and let me know what you think!

Download Convert-OutputForCsv.ps1


This entry was posted in powershell and tagged , , , . Bookmark the permalink.

17 Responses to Avoiding System.Object[] (or Similar Output) when using Export-Csv

  1. Pingback: System.Object[] — Just Knowledge Base

  2. Pingback: System.Object[] — GET-KB

  3. NikoG says:

    Thx Boe,
    your join operator solution really saved my day !
    Kind regards,
    Niko G.

  4. EM says:

    Thank Boe,

    I get nasty error when I try and use it for collecting Event Logs.
    <urn:uuid …
    + ~
    The ‘<‘ operator is reserved for future use.

    I see what it’s like with other output.

  5. Thanks For the Article its really helpful.

  6. fullenw1 says:

    Woaow! Was searching for that solution since ages…
    Many thanks!!!!

  7. Gyz says:

    Download Script Gallery link doesn’t work for me..

  8. Ryan says:

    Awesome little function! Thanks!

  9. Boe,
    Sincere thanks for this excellent bit code! You definitely earned some cred with this critical fix to a missing component of export-csv.

    I’ve been using your function with a series of Get-CimInstance calls and using the Comma output. They all work perfect except for Get-CimInstance Win32_Processor. I kept getting an error that was basically telling me that something was going wrong with parsing the strings. After digging in a bit (adding some Write-Verbose and deconstructing the stringbuilder strings, I found that substituting a single quote for each of the escaped double quotes used with stringbuilder.appendline, solved the error for me.

    This might be attributable to single quote versus double quote escaped content. I haven’t tried other functions, but was wanting to share what I discovered.

  10. wanttolearn says:

    why this line give me error :
    Convert-OutputForCSV -OutputOrder $Output
    but when i use only Convert-OutputForCSV there is no problem (which mean i can’t use the OutputOrder property)?

  11. mandeep says:

    Thank a lot , this is exactly what i was looking for.
    You made my day..!!!

  12. octavmarius says:

    Thank you so much !

  13. rafa says:

    Would you be so kind to tell me, why the following code is not sending the information that i just read to the csv file?

    $a = Get-content C:\Users\xxxxxxx\Documents\Batchtutorial\file.dat -totalcount 1 -join ‘,’
    $a | export-csv “C:\Users\xxxxxx\Documents\Batchtutorial\newcsv.csv”

  14. Michael says:

    Just want to say thanks! This is another example of items that make me read your blog and look forward to the next article!

  15. Pingback: PowerShell – Various Annoyances | /// stealthfield

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s