Add extra rows to CSV under same columns for summary

PowerShell Help
1

Hello everyone,

I am trying to add extra rows to my csv file. I have some data in csv file that i export first then import back the csv and some extra rows ( i do some calculations to just show summary but some reason i am only seeing single row not multiple as i expect;

Here is my script;

#File Location and Create an array
$filelocation=“C:\temp\MyCustomerVMs.csv”
$response_file = @()

####################### # Get-VM Information # Get-Folder "CustomerName" | Get-VM | ` ForEach-Object { $VM = $_ $VMview = $VM | Get-View $Report = "" | Select-Object VMName,VMFQDN,NumCpu,MemoryGB,ProvisionedSpaceGB,ClusterName,ESXHostName $Report.VMName = $VM.name $Report.ProvisionedSpaceGB = $VM.ProvisionedSpaceGB $Report.ESXHostName = $VM.VMHost $Report.ClusterName = ($VM | Get-Cluster).Name $Report.MemoryGB = $VM.MemoryGB $Report.NumCpu = $VM.NumCpu $Report.VMFQDN = $VMview.Guest.hostname $Response_file += $Report $Response_file } #Export the csv file $Response_file | sort -Property VMName | Export-csv "$filelocation" -NoTypeInformation

#Import the csv file
$csv= import-csv “$filelocation”
#Create variables and calculate it
$TotalVMs = $csv.VMName | measure-object -Maximum
$Numcpu = $csv.Numcpu | measure-object -sum
$MemoryTotalGB = $csv.MemoryGB | measure-object -sum
$ProvisionedSpaceGB = $csv.ProvisionedSpaceGB | measure-object -sum
#Create PS Object
$object = New-Object PSObject
$object | Add-Member -Name VMName -Value “Virtual Machines” -MemberType NoteProperty
$object | Add-Member -Name VMName -Value “CPU Consumed vCPU” -MemberType NoteProperty
$object | Add-Member -Name VMName -Value “Memory Consumed (GB)” -MemberType NoteProperty
$object | Add-Member -Name VMName -Value “Storage Consumed TB” -MemberType NoteProperty
$object | Add-Member -Name VMFQDN -Value ($TotalVMs).count -MemberType NoteProperty
$object | Add-Member -Name VMFQDN -Value $Numcpu.sum -MemberType NoteProperty
$object | Add-Member -Name VMFQDN -Value $MemoryTotalGB.sum -MemberType NoteProperty
$object | Add-Member -Name VMFQDN -Value $ProvisionedSpaceGB.sum -MemberType NoteProperty
$object | Export-Csv “$filelocation” -Append -Force


 

 

The results i am getting;

VMName,VMFWDN,OtherColumns,
My Server01,Myserver01.FQDN,OtherValues
My Server02,Myserver02.FQDN,OtherValues
StorageConsumedTB, 15563.1

Seems like only single row gets added rather than multiple rows;
What i would like to see is;

VMName,VMFWDN,OtherColumns,
My Server01,Myserver01.FQDN,OtherValues
My Server02,Myserver02.FQDN,OtherValues
Virtual Machines, 2
CPU Consumed vCPU, 48
Memory Consumed (GB), 920
StorageConsumedTB, 15563.1

is there a better and correct way to do this?

Thank you.

 

2

Looks like what you are trying to make is not a standard csv because the summary data does not align with the column headers. You can certainly make a file like this but if you imported after the summary rows would look like additional servers. With that being said, if you have to make your file this way, I would only do one export at the end.

$TotalVMs = $ResponseFile.VMName | measure-object -Maximum
$Numcpu = $ResponseFile.Numcpu | measure-object -sum
$MemoryTotalGB = $ResponseFile.MemoryGB | measure-object -sum
$ProvisionedSpaceGB = $FesponstFile.ProvisionedSpaceGB | measure-object -sum

$ResponseFile = $ResponseFile | Sort-Object -Property "VMName"
$ResponseFile += "Virtual Machines, $TotalVMs"
$ResponseFile += "Total CPUs $Numcpu"
#continue adding summary data as new elements of the array (rows)

$ResponseFile | Export-Csv $filelocation -NoTypeInformation

 

3

thank you for the recommendation Mike R. It looks like when i continue to adding summary data it is not populating. am i missing something?

Virtual Machines, Microsoft.PowerShell.Commands.GenericObjectMeasureInfo.count
CPU Consumed vCPU Microsoft.PowerShell.Commands.GenericMeasureInfo.sum
Memory Consumed (GB) Microsoft.PowerShell.Commands.GenericMeasureInfo.sum
Storage Consumed TB Microsoft.PowerShell.Commands.GenericMeasureInfo.sum

 

Here is the latest script;

#File Location and Create an array
$filelocation=“C:\temp\MyCustomerVMs.csv”
$response_file = @()

#############################

Get-VM Information

Get-Folder “myCustomer” | Get-VM | `
ForEach-Object {
$VM = $_
$VMview = $VM | Get-View
$Report = “” | Select-Object VMName,VMFQDN,NumCpu,MemoryGB,ProvisionedSpaceGB,ClusterName,ESXHostName
$Report.VMName = $VM.name
$Report.ProvisionedSpaceGB = $VM.ProvisionedSpaceGB
$Report.ESXHostName = $VM.VMHost
$Report.ClusterName = ($VM | Get-Cluster).Name
$Report.MemoryGB = $VM.MemoryGB
$Report.NumCpu = $VM.NumCpu
$Report.VMFQDN = $VMview.Guest.hostname
$Response_file += $Report
$Response_file
}

$TotalVMs = $Response_file.VMName | measure-object -Maximum
$Numcpu = $Response_file.Numcpu | measure-object -sum
$MemoryTotalGB = $Response_file.MemoryGB | measure-object -sum
$ProvisionedSpaceGB = $Response_file.ProvisionedSpaceGB | measure-object -sum

#$Response_file = $Response_file | Sort-Object -Property “VMName”
$Response_file += “Virtual Machines, $TotalVMs.count”
$Response_file += “CPU Consumed vCPU $Numcpu.sum”
$Response_file += “Memory Consumed (GB) $MemoryTotalGB.sum”
$Response_file += “Storage Consumed TB $ProvisionedSpaceGB.sum”
#continue adding summary data as new elements of the array (rows)

$Response_file | Export-Csv $filelocation -NoTypeInformation

 

Thank you.

 

 

 

 

 

4

To interpolate a string with a variable you can’t use the “.” operator. So you’ll have to change the way you create the string. Instead of

$Response_file += “Virtual Machines, $TotalVMs.count”

You’ll have to

$Response_file += “Virtual Machines, $($TotalVMs.count)”

or

$Response_file += “Virtual Machines, {0}” -f $TotalVMs.count

 

5

Thank you, i am getting closer. Now i can see the summary values with this ($Response_file += “Virtual Machines, $($TotalVMs.count)”) such as;

Virtual Machines, 9
CPU Consumed vCPU 112
Memory Consumed (GB) 280
Storage Consumed TB 16582.0547842151

but it is not getting added to end of csv file; Wondering if export-csv does not like it its because it is not uniform values? no errors on the script side. Any ideas?

Thank you again.

 

6

I’m not sure why it wouldn’t add it to the end of the file. For troubleshooting you might want to check the value of the $Response_file variable on the host to see if it has those rows. If so and you’re right that Export-Csv just isn’t working like I think it should, you can simply force it with add-content after the Export-Csv.

Add-Content -Value "Virtual Machines, $($TotalVMs.count)`n" -Path $filelocation

 

7

Add-Member or using Select-Object to create blank object are not very efficient ways to generate objects. Using Powershell v3 and above has the [pscustombject] accelerator. Adding total counts to every row is a bit confusing as normally you calculated something per row, not per object. If I was reading a report I would think there would be TotalX per that VM, not for all of the VMs. However, if you want to add it to the report, use Select-Object or another [pscustomobject] to append the rows. Below is using Select-Object with splatting:

$filelocation=”C:\temp\MyCustomerVMs.csv”

$report = Get-Folder “myCustomer” | Get-VM | ForEach-Object {
    $VM = $_
    $VMview = $VM | Get-View
    #New-Object -TypeName PSObject accelerator
    [pscustomobject] @{
        VMName             = $VM.name
        VMFQDN             = $VMview.Guest.hostname
        NumCpu             = $VM.NumCpu
        MemoryGB           = $VM.MemoryGB
        ProvisionedSpaceGB = $VM.ProvisionedSpaceGB
        ClusterName        = ($VM | Get-Cluster).Name
        ESXHostName        = $VM.VMHost
    }
}

#Splatting
$Response_file = $report |
                 Select-Object -Property  *,
                                          @{Name='TotalVms';Expression={($Report.VMName | measure-object -Maximum).Count}},
                                          @{Name='TotalCpus';Expression={($Report.Numcpu | measure-object -sum).Sum}},
                                          @{Name='TotalMemoryGb';Expression={($Report.VMName | measure-object -Maximum).Sum}},
                                          @{Name='TotalProvSpaceGb';Expression={($Report.ProvisionedSpaceGB | measure-object -sum).Sum}}



$Response_file | Export-Csv $filelocation -NoTypeInformation