Export hashtable to CSV with the key as the column heading

Question

I have a script to create a hashtable with usernames as the key and an array of their groups as the value.

Here is what it looks like:

Name                           Value
----                           -----
user1                          {Domain Users, group2, group3}
user2                          {Domain Users, group4}
user3                          {Domain Users, group2, group3, group4}

How can I export this into a CSV file using the username as the heading?

When I import the CSV file into Excel I want it to look like this:

     A              B             C
  -------------------------------------------
1 | user1         user2         user3
2 | Domain Users  Domain Users  Domain Users
3 | group2        group4        group2
4 | group3                      group3
5 |                             group4

I have played around with Export-Csv but cannot get it to work for this type of hashtable.

Answer 1

To be able to get the arrays as columns you need to "transpose" your hashtable, meaning you need to build individual objects that have the usernames (the keys of the hashtable) as properties. The number of objects you need to create is defined by the maximum number of elements in the arrays.

Basically, you need to chop up your hashtable into multiple hashtables where each hashtable has all keys with just a single string value instead of an array of strings. In essence, you need to transform this:

@{
  'user1' = 'Domain Users', 'group2', 'group3'
  'user2' = 'Domain Users', 'group4'
  'user3' = 'Domain Users', 'group2', 'group3', 'group4'
}

into this:

@{'user1' = 'Domain Users'; 'user2' = 'Domain Users'; 'user3' = 'Domain Users'}
@{'user1' = 'group2'; 'user2' = 'group4'; 'user3' = 'group2'}
@{'user1' = 'group3'; 'user2' = ''; 'user3' = 'group3'}
@{'user1' = ''; 'user2' = ''; 'user3' = 'group4'}

and create objects from these individual hashtables.

$ht = @{
  'user1' = 'Domain Users', 'group2', 'group3'
  'user2' = 'Domain Users', 'group4'
  'user3' = 'Domain Users', 'group2', 'group3', 'group4'
}

# list array lengths and get highest number
$cnt  = $ht.Values |
        ForEach-Object { $_.Count } |
        Sort-Object |
        Select-Object -Last 1
$keys = $ht.Keys | Sort-Object

0..($cnt-1) | ForEach-Object {
  $props = [ordered]@{}
  foreach ($key in $keys) {
    $props[$key] = $ht[$key][$_]
  }
  New-Object -Type PSObject -Property $props
} | Export-Csv 'C:\path\to\output.csv' -NoType

Answer 2

You can create a psobject from the hashtable:

$ht = [ordered]@{
    user1 = @("Domain Users", "group2", "group3")
    user2 = @("Domain Users", "group4")
    user3 = @("Domain Users", "group2", "group3", "group4")
}

New-Object psobject -Property $ht | Export-Csv -Path '.\output.csv' -NoTypeInformation

If the hashtable ($ht) is not ordered, like in the example above, you can sort the header order with Select-Object:

New-Object psobject -Property $ht | Select-Object ($ht.Keys |Sort-Object) | Export-Csv -Path '.\output.csv' -NoTypeInformation

In your example, the values are collections, and will output the typename System.Object[] in the csv file.

You can copy the values to a new hashtable and concatenate the strings with -join before creating the object:

$ht = ($keys = $ht.Keys) | ForEach-Object -Begin { $ht2 = @{} } -Process {
    $ht2[$_] = $ht[$_] -join ", " 
} -End { $ht2 }
New-Object psobject -Property $ht | Select-Object ($keys |Sort-Object) | Export-Csv -Path '.\output.csv' -NoTypeInformation