Get Unique Column and Count from CSV file in Powershell

Question

I have a CSV File called Products.csv

Product_ID,Category
1,A
2,A
3,A
4,B

I want a powershell script that will show me the Unique Categories along with the Count and export to CSV.

i.e.

A,3
B,1

I have used the following code to extract the Unique Categories, but cannot get the Count:

Import-Csv Products.csv -DeLimiter ","|
Select 'Category' -Unique |
Export-Csv Summary.csv -DeLimiter "," -NoTypeInformation

Can anyone help me out? Thanks.

Answer 1

You can use Group-Object to get the count.

Import-Csv Products.csv -DeLimiter "," |
    Group-Object Category | Foreach-Object {
        "{0},{1}" -f $_.Name,$_.Count 
    }

If you want a CSV output of the count, you need headers for your data. Group-Object outputs property Name which is the grouped property value and Count which is the number of items in that group.

Import-Csv Products.csv -DeLimiter "," |
    Group-Object Category | Select-Object Name,Count |
        Export-Csv Summary.csv -Delimiter ',' -NoType

You can take the above code a step further and use Select-Object's calculated properties. Then you can create custom named columns and/or values with expressions.

Import-Csv Products.csv -DeLimiter "," |
    Group-Object Category |
        Select-Object @{n='Product_ID';e={$_.Name}},Count |
            Export-Csv Summary.csv -Delimiter ',' -NoType