将散列表导出为CSV，其中键为列标题 [英] Export hashtable to CSV with the key as the column heading

问题描述

我有一个脚本来创建一个hashtable，用户名作为键，它们的组数组作为值。

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

这是它的样子： p>

Here is what it looks like:

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

如何使用用户名将其导出为CSV文件作为标题？

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

当我将CSV文件导入到Excel中时，我想让它看起来像这样：

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

我已经玩过 Export-Csv ，但是不能让它工作在这种类型hashtable。

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

推荐答案

为了能够将数组作为列，你需要转置你的哈希表，具有用户名（散列表的键）作为属性的单个对象。

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'
}

：

@{'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

这篇关于将散列表导出为CSV，其中键为列标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！