How to filter for specific rows and columns of csv data using Powershell and calculate the sum?

Question

I am new to Powershell and trying to create a script that can filter for specific rows and columns of information in csv file and display the sum for specific columns of information.

The sheet has approx 70 columns and 3000 rows currently

Example format is below

Tracking #  Project Activity    Description         18-Mar  18-Apr  18-May  18-Jun  18-Jul  

Tra_id_000  ABC Development 2 line summary of the work          100 50  10      
Tra_id_001  DEF Development 2 line summary of the work      100     200 50
Tra_id_002  HIJ Testing     2 line summary of the work  50  10  
Tra_id_003  KLM Requirement 2 line summary of the work          100     
Tra_id_004  ABC Testing     2 line summary of the work              100     
Tra_id_005  ABC Other       2 line summary of the work                  1000
Tra_id_006  ABC Testing     2 line summary of the work              1000

I tried to use the example at https://www.pluralsight.com/blog/it-ops/powershell-excel-quick-tip to come up with a example code.

$data=import-csv -path '.\Test.csv'
$data|select-object -first 5|Format-Table -AutoSize
$data|select-object -property Project,'Activity'|group-object -property Prject,'Activity'|select-object -property name,count|sort-object -property name|format-table -autosize

Here I am able to select columns for Project and Activity and display them.

Now I am stuck on next steps in terms of how to filter for a specific Project and Activity and then calculate the sum for corresponding months . For Example, I want to Filter for only Project "ABC" and Activity "Testing" and display the count as well as display the sum for 18-Mar, 18-Apr etc

Any hint on how this can be achieved?

do you have the sample data in CSV format? what you show is not a CSV file ... it may be tab separated, but your Import-CSV line didn't use a -Delimiter parameter. — Lee_Dailey
– Lee_Dailey, Commented Nov 21, 2018 at 5:30

Owain Esau · Accepted Answer · 2018-11-21 05:38:24Z

1

For the filtering you should use the Where-Object cmdlet. In your examples of filtering the project and activity columns you would use:

$data | Where-Object { $_.Project -eq 'ABC' } | Format-Table

$data | Where-Object { $_.Project -eq 'ABC' -and $_.Activity -eq 'Testing' } | Format-Table

Which would return:

Tracking # Project Activity    Description                Mar Apr May Jun  Jul
---------- ------- ---------   ------------               --- --- --- ---  ---
Tra_id_000 ABC     Development 2 line summary of the work         100 50   10
Tra_id_004 ABC     Testing     2 line summary of the work             100
Tra_id_005 ABC     Other       2 line summary of the work                  1000
Tra_id_006 ABC     Testing     2 line summary of the work             1000

and:

Tracking # Project Activity  Description                Mar Apr May Jun  Jul
---------- ------- --------- ------------               --- --- --- ---  ---
Tra_id_004 ABC     Testing   2 line summary of the work             100
Tra_id_006 ABC     Testing   2 line summary of the work             1000

I wasn't sure if you wanted to sum every date column together of individualy but here is an example of an individual sum using your data:

($data | Measure-Object Jun, May -sum).sum

This will return the sum of each row, 1 per line:

1350
200

answered Nov 21, 2018 at 5:38

Owain Esau

1,9222 gold badges23 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Manuj Over a year ago

Thanks. I am looking for sum of filtered data for monthly columns..example for Project ABC and Activity Testing, total should come out as 1100. Is that possible?

Owain Esau Over a year ago

($data | Where-Object { $_.Project -eq 'ABC' -and $_.Activity -eq 'Testing' } | Measure-Object Mar, Apr, May, Jun, Jul -sum).sum

should work

Manuj Over a year ago

I am getting an error when trying to do the sum "Measure-Object : The property "Mar-18" cannot be found in the input for any objects. At line:1 char:91" . I tried with 3/1/2018 instead of Mar-18 also but still the same error

Owain Esau Over a year ago

Did you change the date field names? I renamed them in my script.

Manuj Over a year ago

Yes I changed the fields as per my csv file. In CSV file Mar column is represented as Mar-18 but when i click on the cell I get 3/1/2018. I have tried with both 'Mar-18' as well as '3/1/2018'

|

Collectives™ on Stack Overflow

How to filter for specific rows and columns of csv data using Powershell and calculate the sum?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related