Export hashtable to CSV with the key as the column heading

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

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

Export hashtable to CSV with the key as the column heading

Related

Recent Posts