Powershell-使用Export-csv为多个数组对象创建新行 [英] Powershell - Create new line for multiple array objects using Export-csv

问题描述

我有一个奇怪的故事，我没有看到太多写作。无论如何，这里去。

我正在尝试构建一个数组并将其导出为CSV。问题是，如果返回的结果不止一个，则我不知道如何将其作为新行添加到CSV中。我目前正在使用-join将所有结果都放入同一个单元格中，但这并不是最佳选择。我真正想做的是添加一个新行，并将多余的结果放在同一列下。那有意义吗？这就是我现在拥有的东西：

 ＃抓取所有VM，放入变量
 $ vms = Get-VM 
 
 
＃用于建立报告
 foreach（$ vms中的$ vm）{
新对象PSObject-属性@ {
 VMName = $ vm.Name 
 UsedSpaceGB = $ vm.UsedSpaceGB 
 StorageAllocatedGB =（$ vm.HardDisks.capacitygb | Measure-Object -Sum）.Sum 
 NumberOfCPUs = $ cm.NumCpu 
 MemoryGB = $ vm .MemoryGB 
数据存储= Get-Datastore -VM $ vm 
 Application =（$ vm | Get-Annotation -CustomAttribute Applications -ErrorAction SilentlyContinue）.Value |选择
} |选择VMName，@ {label = Application; expression = {$ _。Application -join，}}，UsedSpaceGB，StorageAllocatedGB，CPU数量，MemoryGB，@ {l = Datastores; e = {$ _。Datastores-加入，}} | Export-Csv-路径C：\script\VMCapacityUsedByApp.csv -NoClobber -Append -NoTypeInformation 
 
} 
  

顺便说一句，这是使用VMware的PowerCLI管理单元。任何帮助是极大的赞赏。

解决方案

好吧，看来数据存储区和应用程序是根据您先前的代码返回数组的唯一字段。假定$ cm.NumCpu应该是$ vm.NumCpu，下面的代码应该执行您想要的操作。它将确定您是否有更多的数据存储或应用程序，然后循环扩展这些字段的阵列，为同一VM列出新的数据存储库和应用程序创建新记录，直到记录用完。我将其设置为仅在第一条记录中列出虚拟机的所有详细信息，但是我敢肯定，您可以弄清楚如何根据需要进行更改。尝试下面的代码，看看它看起来如何：

 ＃抓取所有VM，将其放入变量
 $ vms = Get-VM 
 
 
＃用于构建报告
 foreach（$ vm中的$ vms）{
 $ TempVM =新对象PSObject-属性@ {
 VMName = $ vm.Name 
 UsedSpaceGB = $ vm.UsedSpaceGB 
 StorageAllocatedGB =（$ vm.HardDisks.capacitygb | Measure-Object -Sum）.Sum 
 NumberOfCPUs = $ cm.NumCpu 
 MemoryGB = $ vm.MemoryGB 
数据存储= Get-Datastore -VM $ vm 
 Application =（$ vm | Get-Annotation -CustomAttribute Applications -ErrorAction SilentlyContinue）.Value 
} 
 $ Records = if（$ TempVM.Application.count -gt $ TempVM.Datastores.Count）{$ TempVM.Application.Count} else {$ TempVM.Datastores.Count} 
 $ ExpandedVM = @（ ）
 $ ExpandedVM + = $ TempVM |选择名称，UsedSpaceGB，StorageAllocatedGB，CPU数，MemoryGB，@ {l = Datastores; e = {$ TempVM.Datastores [0]}}，@ {l = Application ; e = {$$ TempVM.Application [ 0]}} 
 for（$ i = 1; $ i -lt $ Records; $ i ++）{$ ExpandedVM + = $ TempVM |选择名称，@ {l = Datastores; e = {$ TempVM。数据存储区[$ i]}}，@ {l = Application; e = {$$ TempVM.Application [$ i]}}}} 
 $ ExpandedVM | Export-Csv-路径C：\script\VMCapacityUsedByApp.csv -NoClobber -Append -NoTypeInformation 
} 
  

也许有一种更优雅的方法，但这至少应该对您有用。我没有要测试的VM机器，也没有使用的插件，所以我组成的数据应该与您要提供的数据一致（所有字段的字符串（数据存储和应用程序除外，它们都有自己的数组）字符串）并最终得到这样的输出：

 名称UsedSpaceGB StorageAllo NumberOfCP MemoryGB Datastores Applicatio 
 catedGB Us n 
 ---- -------------- ----------- ---------- --------- ------- ---------- 
 TestVM 250 500 4 16 Store1 Word 
 TestVM Store2 Excel 
 TestVM Store3访问
 TestVM Outlook 
 TestVM2 487 500 4 32 StoreA WOW 
 TestVM2 StoreB SC2 
 TestVM2 StoreC D3 
 TestVM2 StoreD 
 TestVM2 StoreE 
  

我想这就是您要寻找的。

I have an odd one that I haven't seen much writing on. Anyway, here goes.

I'm trying to build an array and export it to CSV. The problem is, if there is more than one result returned, I can't figure out how to add it to the CSV as a new line. I am currently using a -join to throw all the results into the same cell, but that's not optimal. What I'd really like to do is add a new row and throw the extra results underneath it in the same column. Does that make sense? Here's what I have now:

# Grab all VMs, put into variable
$vms = Get-VM


# Use to build report
foreach ($vm in $vms){
    New-Object PSObject -Property @{
    VMName = $vm.Name
    UsedSpaceGB = $vm.UsedSpaceGB
    StorageAllocatedGB = ($vm.HardDisks.capacitygb | Measure-Object -Sum).Sum
    NumberOfCPUs = $cm.NumCpu
    MemoryGB = $vm.MemoryGB
    Datastores = Get-Datastore -VM $vm
    Application = ($vm | Get-Annotation -CustomAttribute Applications -ErrorAction SilentlyContinue).Value | select
    } | select VMName,@{label="Application";expression={$_.Application -join ","}},UsedSpaceGB,StorageAllocatedGB,NumberOfCPUs,MemoryGB,@{l="Datastores";e={$_.Datastores -join ","}}  | Export-Csv -Path C:\script\VMCapacityUsedByApp.csv -NoClobber -Append -NoTypeInformation

} 

By the way, this is using VMware's PowerCLI snapin. Any help is greatly appreciated.

解决方案

Ok, looks like datastores and applications are the only fields that are going to return arrays according to your previous code. Assuming that the $cm.NumCpu was supposed to be $vm.NumCpu the following code should do what you want. It will figure out if you have more datastores or applications, and then loop through expanding the arrays for those fields creating new records for the same VM listing additional datastores and applications until it runs out of records. I set it to only list all details of a VM on the first record, but I'm sure you can figure out how to alter that if needed. Try this code and see how it looks to you:

# Grab all VMs, put into variable
$vms = Get-VM


# Use to build report
foreach ($vm in $vms){
    $TempVM = New-Object PSObject -Property @{
        VMName = $vm.Name
        UsedSpaceGB = $vm.UsedSpaceGB
        StorageAllocatedGB = ($vm.HardDisks.capacitygb | Measure-Object -Sum).Sum
        NumberOfCPUs = $cm.NumCpu
        MemoryGB = $vm.MemoryGB
        Datastores = Get-Datastore -VM $vm
        Application = ($vm | Get-Annotation -CustomAttribute Applications -ErrorAction SilentlyContinue).Value
    } 
    $Records = if($TempVM.Application.count -gt $TempVM.Datastores.Count){$TempVM.Application.Count}else{$TempVM.Datastores.Count}
    $ExpandedVM = @()
    $ExpandedVM += $TempVM|select Name,UsedSpaceGB,StorageAllocatedGB,NumberOfCPUs,MemoryGB,@{l="Datastores";e={$TempVM.Datastores[0]}},@{l="Application";e={$TempVM.Application[0]}}
    for($i=1;$i -lt $Records;$i++){$ExpandedVM += $TempVM|select Name,@{l="Datastores";e={$TempVM.Datastores[$i]}},@{l="Application";e={$TempVM.Application[$i]}}}
    $ExpandedVM | Export-Csv -Path C:\script\VMCapacityUsedByApp.csv -NoClobber -Append -NoTypeInformation
} 

There may be a more elegant way to do it, but that should be functional for you at the very least. I don't have VM machines to test against, or the plugin you use, so I made up data that should be in line with what you're feeding it (strings for all fields except datastores and application both of which have their own array of strings) and ended up with output like this:

Name        UsedSpaceGB StorageAllo NumberOfCP MemoryGB   Datastores Applicatio
                        catedGB     Us                               n         
----        ----------- ----------- ---------- --------   ---------- ----------
TestVM      250         500         4          16         Store1     Word      
TestVM                                                    Store2     Excel     
TestVM                                                    Store3     Access    
TestVM                                                               Outlook   
TestVM2     487         500         4          32         StoreA     WoW       
TestVM2                                                   StoreB     SC2       
TestVM2                                                   StoreC     D3        
TestVM2                                                   StoreD               
TestVM2                                                   StoreE               

That is what you were looking for I think.

这篇关于Powershell-使用Export-csv为多个数组对象创建新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！