How to Get Unique Values from CSV Using PowerShell?

One of my clients was working on a CSV file, and they were required to find unique values from that CSV file. I suggested using PowerShell and told them a few methods. In this tutorial, I will explain to you four simple and useful methods to get unique values from CSV using PowerShell.

Table of Contents

Get Unique Values from CSV Using PowerShell

PowerShell provides various cmdlets to work with CSV files. In combination with a few other cmdlets, you can be able to get unique values from a CSV file in PowerShell.

Here are the methods with examples.

For all the methods, I am using the below CSV file, which has the below content. You can even download the CSV file and use it.

Name,Age
Alice,30
Bob,25
Alice,30
Charlie,35
Bob,25

Method 1: Using Select-Object -Unique

One way to get unique values from a CSV file in PowerShell is by using the Select-Object cmdlet with the -Unique parameter.

This method is also very simple to use, and here is the complete PowerShell script to extract unique rows based on the Name column.

# Import the CSV file
$data = Import-Csv -Path "C:\MyFolder\data.csv"

# Select unique rows based on the 'Name' column
$uniqueData = $data | Select-Object -Property Name -Unique

# Output the unique rows
$uniqueData

This script will output:

Name
----
Alice
Bob
Charlie

I executed the above script using VS code and you can see the output in the screenshot below:

Get Unique Values from CSV Using PowerShell

Method 2: Using Group-Object

Another efficient way to get unique values from a CSV file in PowerShell is by using the Group-Object cmdlet.

Here also, we will use the same data.csv file. We can group the rows by the Name column and then select the first instance of each group:

Below is the complete PowerShell script.

# Import the CSV file
$data = Import-Csv -Path "C:\MyFolder\data.csv"

# Group by 'Name' and select the first instance of each group
$uniqueData = $data | Group-Object -Property Name | ForEach-Object { $_.Group[0] }

# Output the unique rows
$uniqueData

This script will output:

Name   Age
----   ---
Alice  30
Bob    25
Charlie 35

After executing the above PowerShell script, I got the required output, and you can see the output in the screenshot below:

How to Get Unique Values from CSV Using PowerShell

Method 3: Using Sort-Object and Get-Unique

The PowerShell Sort-Object cmdlet can be combined with Get-Unique to sort the data first and then remove duplicates. This method requires the input data to be sorted.

Using the same data.csv file, you can sort by the Name column and then get unique values; here is the complete script.

# Import the CSV file
$data = Import-Csv -Path "C:\MyFolder\data.csv"

# Sort by 'Name' and then get unique values
$uniqueData = $data | Sort-Object -Property Name | Get-Unique -AsString

# Output the unique rows
$uniqueData

This script will output:

Name   Age
----   ---
Alice  30
Bob    25
Charlie 35

Method 4: Using HashSet

Here is the final method to get unique values from an Excel file in PowerShell using HashSet.

Using the same data.csv file, you can use a HashSet to store unique values like below:

# Import the CSV file
$data = Import-Csv -Path "C:\MyFolder\data.csv"

# Create a HashSet to store unique names
$hashSet = New-Object System.Collections.Generic.HashSet[string]

# Iterate over each row and add unique names to the HashSet
foreach ($row in $data) {
    [void]$hashSet.Add($row.Name)
}

# Output the unique names
$hashSet

This script will output:

Alice
Bob
Charlie

Conclusion

In this PowerShell tutorial, I have explained how to get unique values from a CSV file using PowerShell. You can use various methods like the Select-Object -Unique, Group-Object, Sort-Object with Get-Unique, or a HashSet, etc. I hope this helps you.

Get Unique Values from CSV Using PowerShell

Method 1: Using Select-Object -Unique

Method 2: Using Group-Object

Method 3: Using Sort-Object and Get-Unique

Method 4: Using HashSet

Conclusion

100 POWERSHELL CMDLETS E-BOOK