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.
[pscustomobject]@{
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.
[pscustomobject]@{
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.
[pscustomobject]@{
First = 'Boe'
Last = 'Prox'
ExtraInfo = (@(1,3,5,6) -join ',')
State = 'NE'
IPs = (@('111.222.11.22','55.12.89.125','125.48.2.1','145.23.15.89','123.12.1.0') -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().
[pscustomobject]@{
First = 'Boe'
Last = 'Prox'
ExtraInfo = (@(1,3,5,6) | Out-String).Trim()
State = 'NE'
IPs = (@('111.222.11.22','55.12.89.125','125.48.2.1','145.23.15.89','123.12.1.0') | 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',
'DefaultIPGateway','DNSServerSearchOrder'
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
Convert-OutputForCSV.ps1
), but some were just really hard to read or others were doing things that I wouldn’t consider to be a good practice. 
).
Learn Powershell | Achieve More 