excel - Export hashtable to CSV with the key as the column heading -
i have script create hashtable usernames key , array of groups value.
here looks like:
name value ---- ----- user1 {domain users, group2, group3} user2 {domain users, group4} user3 {domain users, group2, group3, group4} how can export csv file using username heading?
when import csv file excel want this:
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 export-csv cannot work type of hashtable.
to able arrays columns need "transpose" hashtable, meaning need build individual objects have usernames (the keys of hashtable) properties. number of objects need create defined maximum number of elements in arrays.
basically, need chop hashtable multiple hashtables each hashtable has keys single string value instead of array of strings. in essence, need 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 these individual hashtables.
$ht = @{ 'user1' = 'domain users', 'group2', 'group3' 'user2' = 'domain users', 'group4' 'user3' = 'domain users', 'group2', 'group3', 'group4' } # list array lengths , 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
Comments
Post a Comment