Add extra rows to CSV under same columns for summary

Welcome Forums General PowerShell Q&A Add extra rows to CSV under same columns for summary

Viewing 6 reply threads
  • Author
    Posts
    • #284806
      Participant
      Topics: 4
      Replies: 4
      Points: 42
      Rank: Member

      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.

       

    • #284809
      Participant
      Topics: 5
      Replies: 253
      Points: 1,007
      Helping Hand
      Rank: Community Hero

      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.

       

    • #284824
      Participant
      Topics: 4
      Replies: 4
      Points: 42
      Rank: Member

      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.

       

       

       

       

       

    • #284830
      Participant
      Topics: 5
      Replies: 253
      Points: 1,007
      Helping Hand
      Rank: Community Hero

      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

      You’ll have to

      or

       

    • #284836
      Participant
      Topics: 4
      Replies: 4
      Points: 42
      Rank: Member

      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.

       

    • #284863
      Participant
      Topics: 5
      Replies: 253
      Points: 1,007
      Helping Hand
      Rank: Community Hero

      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.

       

      • This reply was modified 1 week, 5 days ago by Mike R..
    • #284869
      Participant
      Topics: 17
      Replies: 1951
      Points: 3,988
      Helping Hand
      Rank: Community Hero

      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:

Viewing 6 reply threads
  • You must be logged in to reply to this topic.