PowerShell: Import CSV File, Count Group Sizes, And Output To Original File Appending New Column With Counts

Question

I have a test input csv file, as follows:

ID;Name;Level
1;Alpha;A
2;Bravo;A
3;Charlie;A
4;Delta;A
5;Echo;A
6;Foxtrot;A
7;Golf;A
1;Hotel;B
2;India;B
3;Juliet;B
1;Kilo;C
2;Lima;C
3;Mike;C
4;November;C
5;Oscar;C

and I would like to generate the following output file:

ID;Name;Level;Number
1;Alpha;A;7
2;Bravo;A;7
3;Charlie;A;7
4;Delta;A;7
5;Echo;A;7
6;Foxtrot;A;7
7;Golf;A;7
1;Hotel;B;3
2;India;B;3
3;Juliet;B;3
1;Kilo;C;5
2;Lima;C;5
3;Mike;C;5
4;November;C;5
5;Oscar;C;5

I have the following code snippet which, though it calculates the correct group counts, does not output anything to csv:

Import-Csv '.\TestInput.csv' -Delimiter ';' | 
   Group-Object 'Level' | 
   Select-Object -Expand Count | 
   Export-Csv -NoTypeInformation '.\TestOutput.csv'

What am I missing?

I don't see how the outputs you desire come from the input that you show. Did you only show us part of the CSV file? — Walter Mitty
– Walter Mitty, Commented Jun 29, 2020 at 10:11

jfrmilner · Accepted Answer · 2020-06-29 10:29:28Z

1

Break the Number count out, give this a try

$data = Import-Csv '.\TestInput.csv' -Delimiter ';'
$levelCount = $data | Group-Object -Property 'Level' -AsHashTable
$data | Add-Member -MemberType ScriptProperty -Name Number -Value {$levelCount[$this.Level].count} -PassThru | Export-Csv -NoTypeInformation '.\TestOutput.csv'

answered Jun 29, 2020 at 10:29

jfrmilner

1,86814 silver badges15 bronze badges

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

1 Comment

matekus Over a year ago

Thanks for the benefit of your expertise and the elegant solution.

Collectives™ on Stack Overflow

PowerShell: Import CSV File, Count Group Sizes, And Output To Original File Appending New Column With Counts

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related