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












1















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?










share|improve this question























  • 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 '18 at 5:30
















1















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?










share|improve this question























  • 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 '18 at 5:30














1












1








1








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?










share|improve this 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?







powershell






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 21 '18 at 4:39









ManujManuj

1311111




1311111













  • 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 '18 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 '18 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 '18 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 '18 at 5:30












1 Answer
1






active

oldest

votes


















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





share|improve this answer
























  • 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 '18 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 '18 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 '18 at 7:31













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

    – Owain Esau
    Nov 21 '18 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 '18 at 7:37











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















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

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









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





share|improve this answer
























  • 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 '18 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 '18 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 '18 at 7:31













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

    – Owain Esau
    Nov 21 '18 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 '18 at 7:37
















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





share|improve this answer
























  • 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 '18 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 '18 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 '18 at 7:31













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

    – Owain Esau
    Nov 21 '18 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 '18 at 7:37














1












1








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





share|improve this answer













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






share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 21 '18 at 5:38









Owain EsauOwain Esau

880718




880718













  • 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 '18 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 '18 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 '18 at 7:31













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

    – Owain Esau
    Nov 21 '18 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 '18 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 '18 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 '18 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 '18 at 7:31













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

    – Owain Esau
    Nov 21 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 at 7:31















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

– Owain Esau
Nov 21 '18 at 7:34





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

– Owain Esau
Nov 21 '18 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 '18 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 '18 at 7:37


















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














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





















































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







Popular posts from this blog

'app-layout' is not a known element: how to share Component with different Modules

android studio warns about leanback feature tag usage required on manifest while using Unity exported app?

WPF add header to Image with URL pettitions [duplicate]