CODEWITHSUNDEEP
powershell
Manuj
Manuj

Reputation: 296

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

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?

Upvotes: 1

Views: 8783

Answers (1)

Owain Esau
Owain Esau

Reputation: 1922

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

Upvotes: 1

Related Questions