Search code examples
powershellobjectexport-to-csvmixed

How to export mixed type objects to csv file?


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.


Solution

  • 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.