将哈希表值的属性导出到CSV文件 [英] Export a hashtable-valued property to a CSV file

问题描述

我正在运行Test-AdfsServerHealth(

但是PowerShell显示:

Name             : TestServiceAccountProperties
Result           : Pass
Detail           :
Output           : {AdfsServiceAccount, AdfsServiceAccountDisabled, AdfsServiceAccountLockedOut,
                   AdfsServiceAccountPwdExpired...}
ExceptionMessage :

我正在运行的实际命令是:

$ServerResult = Test-AdfsServerHealth

解决方案

tl; dr :

Test-AdfsServerHealth | 
  Select-Object Name, Result, Detail, @{ 
    n='Output'
    e={ $_.prop2.GetEnumerator().ForEach({ '{0}={1}' -f $_.Key, $_.Value }) -join ' ' } 
  } | ExportTo-Csv out.csv

上面的代码将每个.Output哈希表的条目序列化为单行字符串，该字符串由以空格分隔的<key>=<value>对(PSv4 +语法)组成，应在CSV输出中正常工作.

---

由于 CSV是文本格式，因此 PowerShell通过调用对象的.ToString()方法来序列化要导出的对象.

复杂的对象(例如[hashtable]实例)通常仅产生其完整类型名称(System.Collections.Hashtable)，对于.ToString()而言，这在CSV中没有用.

一个简化的示例(我使用的是ConvertTo-Csv，但是该示例类似地适用于Export-Csv):

# Create a custom object whose .col2 property is a hashtable with 2 
# sample entries and convert it to CSV
PS> [pscustomobject] @{ prop1 = 1; Output = @{ name='foo'; ID=666 } } | ConvertTo-Csv

"prop1","Output"
"1","System.Collections.Hashtable"

如果Test-AdfsServerHealth中的所有输出对象的.Output属性都具有相同的哈希表结构，则可以尝试通过使其自身的条目列为 flatten 哈希表，但是听起来并非如此.

因此，您必须手动将哈希表转换为适合单个CSV列的文本表示形式:

您可以使用Select-Object和 calculated属性来执行此操作，该属性将为您执行转换，但是您需要确定在CSV文件的上下文中有意义的文本表示形式./p>

在以下示例中，创建了由空格分隔的<key>=<value>对组成的单行字符串(PSv4 +语法).

[pscustomobject] @{ prop1 = 1; Output = @{ name='foo'; ID=666 } } | 
  Select-Object prop1, @{ 
    n='Output'
    e={ $_.prop2.GetEnumerator().ForEach({ '{0}={1}' -f $_.Key, $_.Value }) -join ' ' } 
  } | ConvertTo-Csv

^{有关创建计算出的prop2属性的哈希表格式的说明，请参见}答案我的.

上面的产量:

"prop1","prop2"
"1","ID=666 name=foo"

但是，请注意，如果哈希表中的值再次是仅序列化为其类型名称的复杂对象，则您必须递归地应用方法 .

---

可选阅读:将哈希表属性展平为各个列

如果要导出到CSV文件的对象的哈希表值属性都具有相同的结构，则可以选择使哈希表条目各自具有自己的输出列.

让我们接受以下示例输入:2个自定义对象的集合，其.prop2值是具有统一键(条目)集的哈希表:

$coll = [pscustomobject] @{ prop1 = 1; prop2 = @{ name='foo1'; ID=666 } },
        [pscustomobject] @{ prop1 = 2; prop2 = @{ name='foo2'; ID=667 } }

如果您预先知道(感兴趣的)键名，则可以使用显式的计算属性列表来创建各个列:

$coll | select prop1, @{ n='name'; e={ $_.prop2.name } }, @{ n='ID'; e={ $_.prop2.ID } } |
  ConvertTo-Csv

上面的代码产生了以下内容，表明哈希表条目成为它们自己的列name和ID:

"prop1","name","ID"
"1","foo1","666"
"2","foo2","667"

如果您不不预先知道密钥名称，则需要更高级的技术:

# Create the list of calculated properties dynamically, from the 1st input
# object's .prop2 hashtable.
$propList = foreach ($key in $coll[0].prop2.Keys) {
  # The script block for the calculated property must be created from a 
  # *string* in this case, so we can "bake" the key name into it.
  @{ n=$key; e=[scriptblock]::Create("`$_.prop2.$key") } 
}

$coll | Select-Object (, 'prop1' + $propList) | ConvertTo-Csv

这将产生与前一条命令相同的输出，并带有固定的已计算属性列表.

I'm running the Test-AdfsServerHealth (Ref.)

The problem is, one of the output values (value name Output) is an array that shows up as System.Collection.Hashtable and I'm trying to find a way to get this in a neat Excel format.

For instance, this is one of the actual values on the CSV when I export:

Name    Result  Detail  Output
TestServiceAccountProperties    Pass    "" System.Collections.Hashtable

But PowerShell displays:

Name             : TestServiceAccountProperties
Result           : Pass
Detail           :
Output           : {AdfsServiceAccount, AdfsServiceAccountDisabled, AdfsServiceAccountLockedOut,
                   AdfsServiceAccountPwdExpired...}
ExceptionMessage :

The actual command I'm running is:

$ServerResult = Test-AdfsServerHealth

解决方案

tl;dr:

Test-AdfsServerHealth | 
  Select-Object Name, Result, Detail, @{ 
    n='Output'
    e={ $_.prop2.GetEnumerator().ForEach({ '{0}={1}' -f $_.Key, $_.Value }) -join ' ' } 
  } | ExportTo-Csv out.csv

The above serializes each .Output hashtable's entries into single-line string composed of space-separated <key>=<value> pairs (PSv4+ syntax) that should work reasonably well in CSV output.

---

Since CSV is a text format, PowerShell serializes objects to be exported by calling their .ToString() method.

Complex objects such as [hashtable] instances often yield just their full type name (System.Collections.Hashtable) for .ToString(), which isn't useful in a CSV.

A simplified example (I'm using ConvertTo-Csv, but the example applies analogously to Export-Csv):

# Create a custom object whose .col2 property is a hashtable with 2 
# sample entries and convert it to CSV
PS> [pscustomobject] @{ prop1 = 1; Output = @{ name='foo'; ID=666 } } | ConvertTo-Csv

"prop1","Output"
"1","System.Collections.Hashtable"

If all output objects from Test-AdfsServerHealth had the same hashtable structure in their .Output property, you could try to flatten the hashtable by making its entries columns in their own right, but it sounds like that is not the case.

You must therefore manually transform the hashtable into a text representation that fits into a single CSV column:

You can do this with Select-Object and a calculated property that performs the transformation for you, but you need to decide on a text representation that makes sense in the context of a CSV file.

In the following example, a single-line string composed of space-separated <key>=<value> pairs is created (PSv4+ syntax).

[pscustomobject] @{ prop1 = 1; Output = @{ name='foo'; ID=666 } } | 
  Select-Object prop1, @{ 
    n='Output'
    e={ $_.prop2.GetEnumerator().ForEach({ '{0}={1}' -f $_.Key, $_.Value }) -join ' ' } 
  } | ConvertTo-Csv

^{For an explanation of the hashtable format that creates the calculated prop2 property, see this answer of mine.}

The above yields:

"prop1","prop2"
"1","ID=666 name=foo"

Note, however, that if the values in your hashtables are again complex objects that serialize to their type name only, you'd have to apply the approach recursively.

---

Optional reading: Flattening a hashtable property into individual columns

If the hashtable-valued properties of the objects to export to a CSV file all have the same structure, you can opt to make the hashtable entries each their own output column.

Let's take the following sample input: a collection of 2 custom objects whose .prop2 value is a hashtable with a uniform set of keys (entries):

$coll = [pscustomobject] @{ prop1 = 1; prop2 = @{ name='foo1'; ID=666 } },
        [pscustomobject] @{ prop1 = 2; prop2 = @{ name='foo2'; ID=667 } }

If you know the key names (of interest) up front, you can simply use an explicit list of calculated properties to create the individual columns:

$coll | select prop1, @{ n='name'; e={ $_.prop2.name } }, @{ n='ID'; e={ $_.prop2.ID } } |
  ConvertTo-Csv

The above yields the following, showing that the hashtable entries became their own columns, name and ID:

"prop1","name","ID"
"1","foo1","666"
"2","foo2","667"

More advanced techniques are required if you do not know the key names up front:

# Create the list of calculated properties dynamically, from the 1st input
# object's .prop2 hashtable.
$propList = foreach ($key in $coll[0].prop2.Keys) {
  # The script block for the calculated property must be created from a 
  # *string* in this case, so we can "bake" the key name into it.
  @{ n=$key; e=[scriptblock]::Create("`$_.prop2.$key") } 
}

$coll | Select-Object (, 'prop1' + $propList) | ConvertTo-Csv

This yields the same output as the previous command with the fixed list of calculated properties.

这篇关于将哈希表值的属性导出到CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！