Exporting complex Hashtable with string key and PSObject value to .csv [PowerShell]

Question

I've got this hashtable of users called $userTable which is of the form:.

<key = string, value = PSObject>

Objects are added to the hashtable as follows:

$userTable = @{}

$id="1056456"
$user = New-Object PSObject -Property @{
    Name="Test Name"
    Email="Test Email"
    Systems = New-Object System.Collections.ArrayList
}

$system1 = New-Object PSObject -Property @{
   Name="System 1"
   Admin=$true
   Status="Active"
}

$system2 = New-Object PSObject -Property @{
   Name="System 2"
   Admin=$false
   Status="Inactive"
}

$user.Systems.Add($system1) | Out-Null
$user.Systems.Add($system2) | Out-Null

$userTable.Add($id, $user)

So for each entry in $userTable, there is a name, an email and a list of systems associated with that user.

What I want to do is export this data to .csv such that it takes the following form:

ID, Name, Email, System Count, System1 Name, System1 Admin, System1 Status, System2 Name, etc..

There may be more than 2 systems per user.

I cannot get my head around how to export this to a .csv file.

Any help would be appreciated, thanks.

I was thinking of using something along the lines of:

$userTable.GetEnumerator() |
Select-Object -Property @{N='ID';           E={$_.Key}},
                        @{N='Name';         E={$_.Value.Name}},
                        @{N='Email';        E={$_.Value.Email}},
                        @{N='System Count'; E={$_.Value.Systems.Count}} |
    Export-Csv -NoTypeInformation -Path C:\Documents\test.csv

But that does not include the System Array List (yet)

Answer 1

I have to agree with @MathiasR.Jessen's comments. This comes up a lot on SO. CSV is a convenient format and often the first choice for persistent storage and/or to relay information between programs or systems. However, it's flat and therefore a pretty bad analog for rich hierarchical and/or complex objects.

If the objects were only 1 level deep, for example, if .Systems were itself a simple array, you could sub-delimit the field. A great example of this is MS Exchange message tracking logs which although "," delimited, sub-delimit the Recipients field on a ";". However, it doesn't go further than that.

In short, anything you try to do to get around the limitations of the CSV format will end up increasing the complexity of your code. You would have to labor to write the CSV just so... Then labor more to re-import it elsewhere just so...

JSON & CliXML are your alternatives. They are both very good for storing text-based representations of even complex objects. Such examples might look something like:

$userTable.GetEnumerator() |
Select-Object -Property @{N='ID';           E={$_.Key}},
                        @{N='Name';         E={$_.Value.Name}},
                        @{N='Email';        E={$_.Value.Email}},
                        @{N='System Count'; E={$_.Value.Systems.Count}},
                        @{N='Systems';      E={$User.Systems}} |
ConvertTo-Json | 
Add-Content -Path "C:\temp\JSONOutput.json"

Since we don't have the greater body of code, I'm not sure if the 'Systems' property is being populated appropriately. However, you are simply setting up the property systems to have a value which is an ArrayList the elements of which are other objects.

JSON is obviously very popular but CliXML is more native to PowerShell. The CliXML alternative would look like:

$userTable.GetEnumerator() |
Select-Object -Property @{N='ID';           E={$_.Key}},
                        @{N='Name';         E={$_.Value.Name}},
                        @{N='Email';        E={$_.Value.Email}},
                        @{N='System Count'; E={$_.Value.Systems.Count}},
                        @{N='Systems';      E={$User.Systems}} |
Export-Clixml c:\temp\CliXML_Export.xml

And, to use the data elsewhere, respectively would look something like:

# For JSON:
$SomeVar = (Get-Content "C:\temp\JSONOutput.json") | ConvertFrom-Json

# For CliXml:
$SomeVar = Import-CliXml c:\temp\CliXML_Export.xml

Note: the (...) around Get-Content in the Json example. I believe that's required in PowerShell 5.1. Though I think that's been fixed in 7+, where you can just drop the parens. I'm not sure if that limitation is in 6.x.

Note: Generally when serializing objects as discussed above, the methods will be stripped off. In the case of CliXml, it's the same format used to serialize objects over the wire in PowerShell remoting, which has the same limitation. That said, for the purposes demonstrated here these formats generally have enough fidelity to be usable down the line.