How to export mixed type objects to csv file?

Question

I'm writing a script that returns a list of objects that most of them have different number of properties. When I print it in the console everything is OK, but when I try to export to CSV only those fields that are common in all objects get exported. All others are cropped.

I use the Add-Member cmdlet to add more properties but not all of the objects get the same number of properties.

For example I try to export 2 objects where one is like this:

FirstObject:{
  Network0:nic1,
  Network1:nic2,
  Network2:nic3,
  Network3:nic4,
  Name:VirtualMachine1
}

SecondObject:{
  Network0:nic1,
  Network1:nic2,
  Name:VirtualMachine1
}

The Network property is added with Add-Member cmdlet. The problem I get when exporting to CSV is that Network2 and Network3 properties from the first object are cropped and all the columns I get is Network0, Network1, and Name.

What I would like to know is there a way to export all the properties and if one of the objects doesn't have the property, just assign $null?

P.S. I have a solution just to add those fields manually with a loop, but I was wondering maybe there is a cleaner solution built in PowerShell which I missed?

Update:

I found out that it provides the same columns to the file that are in the first object. All other fields are ignored. So to be more exact I need all columns in all objects. If some objects do not have the field, then it should be printed empty.

It would be helpful to see the code you are currently using to do the export. — Mark Wragg, Mar 07 '17 at 09:08
I am pipelining all those objects to --> | export-csv -notypeinformation exported.csv — SokIsKedu, Mar 07 '17 at 09:10

Anton Krouglov · Accepted Answer · 2017-03-13T12:23:49.723

Just a few lines of code that add missing properties.

#sample setup
$one = [pscustomobject]@{
    Network0='nic1'
    Network1='nic2'
    Network2='nic3'
    Network3='nic4'
    Name='VirtualMachine1'
}


$two = [pscustomobject]@{
    Network0='nic1'
    Network1='nic2'
    Name='VirtualMachine2'
}

$three = [pscustomobject]@{
    Network0='nic1'
    Name='VirtualMachine3'
}

$export = ($one,$two,$three)

#build list of all properties available to $allProps
$export | % -begin { $allProps = @() } -process { $allProps = [linq.enumerable]::union([object[]](($_.psobject.Properties).Name), [object[]]$allProps) }

#convert each object in $export to new custom object having all properties and put to $result
$export | % -begin { $result = @() } -process { $__ = $_; $o = @{ }; $allProps | %{ $o += @{ $_ = $__.$_ } }; $result+=[pscustomobject]$o }

#export $result to csv
$result | Export-Csv $env:TEMP\export.csv -NoTypeInformation -Force

Get-Content $env:TEMP\export.csv

"Network1", "Network3", "Network0", "Name", "Network2"
"nic2", "nic4", "nic1", "VirtualMachine1", "nic3"
"nic2",, "nic1", "VirtualMachine2",
 ,, "nic1", "VirtualMachine3",
>> Script Ended

Things to note:

[linq.enumerable]::union is used to easy build list of all available properties across all objects.
($_.psobject.Properties).Name is shortcut to @($_.psobject.Properties | select -ExpandProperty Name), it contains array of property names
$__ = $_ is a trick for nested loop
$o += @{ $_ = $__.$_ } adds key-value pairs to output object; trick here is that even if property $_='nic4' does not exists in $__ export object, powershell does not throw error and returns $null. Note that this will not work when Set-StrictMode is set -Version 2 or later.

Thank you very much! Probably the hardest one liners I've seen. Thanks for those things to note, really helpful :) — SokIsKedu, Mar 08 '17 at 11:47

How to export mixed type objects to csv file?

1 Answers1