Home > Back-end >  Powershell csv calculate total sum
Powershell csv calculate total sum

Time:09-16

I am currently working on powershell. Powershell is new for me so its kind of hard to figure out this one.

I have three headers in my csv files.

Headers include: Name, MessageCount and Direction.

Names are email addresses and those addresses are all the same. Direction have "Inbound" and "Outbound". MessageCount are bunch of diffrent numbers:

Overview

I want to calculate those number so i get "Inbound" and "Outbound" Totals and emails on those rows.

I am trying to foreach loop out MessageCount and calculate those together it will only give me output like this :

MessageCount

CodePudding user response:

Try something like this:

$data = Import-Csv "path-to-your-csv-file";

$data | group Name 
      | select Name, 
          @{n = "Inbound"; e = { 
            (($_.Group | where Direction -eq "Inbound").MessageCount | Measure-Object -Sum).Sum } 
          }, 
          @{n = "Outbound"; e = { 
            (($_.Group | where Direction -eq "Outbound").MessageCount | Measure-Object -Sum).Sum } 
          }

Code explanation

group Name groups results by property Name - in this case, email address. More here

select allows select property from object or create custom with @{n="";e={}}. More here

($_.Group | where Direction -eq "Outbound").MessageCount gets data from the group, searches for rows with Direction equal to Outbound and then gets the MessageCount from found rows.

Measure-Object -Sum takes array and creates object with properties ie. sum of values in array, so we get sum of MessageCount and return as custom property in object.

  • Related