How to filter for specific rows and columns of csv data using Powershell and calculate the sum?
up vote
1
down vote
favorite
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?
powershell
add a comment |
up vote
1
down vote
favorite
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?
powershell
do you have the sample data in CSV format? what you show is not a CSV file ... it may be tab separated, but yourImport-CSV
line didn't use a-Delimiter
parameter.
– Lee_Dailey
Nov 21 at 5:30
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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?
powershell
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?
powershell
powershell
asked Nov 21 at 4:39
Manuj
1261111
1261111
do you have the sample data in CSV format? what you show is not a CSV file ... it may be tab separated, but yourImport-CSV
line didn't use a-Delimiter
parameter.
– Lee_Dailey
Nov 21 at 5:30
add a comment |
do you have the sample data in CSV format? what you show is not a CSV file ... it may be tab separated, but yourImport-CSV
line didn't use a-Delimiter
parameter.
– Lee_Dailey
Nov 21 at 5:30
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
Nov 21 at 5:30
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
Nov 21 at 5:30
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
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
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?
– Manuj
Nov 21 at 6:13
($data | Where-Object { $_.Project -eq 'ABC' -and $_.Activity -eq 'Testing' } | Measure-Object Mar, Apr, May, Jun, Jul -sum).sum
should work
– Owain Esau
Nov 21 at 6:24
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
– Manuj
Nov 21 at 7:31
Did you change the date field names? I renamed them in my script.
– Owain Esau
Nov 21 at 7:34
1
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'
– Manuj
Nov 21 at 7:37
|
show 2 more comments
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
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
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?
– Manuj
Nov 21 at 6:13
($data | Where-Object { $_.Project -eq 'ABC' -and $_.Activity -eq 'Testing' } | Measure-Object Mar, Apr, May, Jun, Jul -sum).sum
should work
– Owain Esau
Nov 21 at 6:24
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
– Manuj
Nov 21 at 7:31
Did you change the date field names? I renamed them in my script.
– Owain Esau
Nov 21 at 7:34
1
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'
– Manuj
Nov 21 at 7:37
|
show 2 more comments
up vote
1
down vote
accepted
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
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?
– Manuj
Nov 21 at 6:13
($data | Where-Object { $_.Project -eq 'ABC' -and $_.Activity -eq 'Testing' } | Measure-Object Mar, Apr, May, Jun, Jul -sum).sum
should work
– Owain Esau
Nov 21 at 6:24
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
– Manuj
Nov 21 at 7:31
Did you change the date field names? I renamed them in my script.
– Owain Esau
Nov 21 at 7:34
1
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'
– Manuj
Nov 21 at 7:37
|
show 2 more comments
up vote
1
down vote
accepted
up vote
1
down vote
accepted
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
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 at 5:38
Owain Esau
796417
796417
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?
– Manuj
Nov 21 at 6:13
($data | Where-Object { $_.Project -eq 'ABC' -and $_.Activity -eq 'Testing' } | Measure-Object Mar, Apr, May, Jun, Jul -sum).sum
should work
– Owain Esau
Nov 21 at 6:24
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
– Manuj
Nov 21 at 7:31
Did you change the date field names? I renamed them in my script.
– Owain Esau
Nov 21 at 7:34
1
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'
– Manuj
Nov 21 at 7:37
|
show 2 more comments
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?
– Manuj
Nov 21 at 6:13
($data | Where-Object { $_.Project -eq 'ABC' -and $_.Activity -eq 'Testing' } | Measure-Object Mar, Apr, May, Jun, Jul -sum).sum
should work
– Owain Esau
Nov 21 at 6:24
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
– Manuj
Nov 21 at 7:31
Did you change the date field names? I renamed them in my script.
– Owain Esau
Nov 21 at 7:34
1
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'
– Manuj
Nov 21 at 7:37
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?
– Manuj
Nov 21 at 6:13
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?
– Manuj
Nov 21 at 6:13
($data | Where-Object { $_.Project -eq 'ABC' -and $_.Activity -eq 'Testing' } | Measure-Object Mar, Apr, May, Jun, Jul -sum).sum
should work– Owain Esau
Nov 21 at 6:24
($data | Where-Object { $_.Project -eq 'ABC' -and $_.Activity -eq 'Testing' } | Measure-Object Mar, Apr, May, Jun, Jul -sum).sum
should work– Owain Esau
Nov 21 at 6:24
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
– Manuj
Nov 21 at 7:31
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
– Manuj
Nov 21 at 7:31
Did you change the date field names? I renamed them in my script.
– Owain Esau
Nov 21 at 7:34
Did you change the date field names? I renamed them in my script.
– Owain Esau
Nov 21 at 7:34
1
1
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'
– Manuj
Nov 21 at 7:37
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'
– Manuj
Nov 21 at 7:37
|
show 2 more comments
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53405359%2fhow-to-filter-for-specific-rows-and-columns-of-csv-data-using-powershell-and-cal%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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
Nov 21 at 5:30