I would like to select only unique items from a CSV file in PowerShell

Question

I have a CSV file without header or column name. The data is a list of IP addresses

 112.64.127.98
 112.64.127.98
 112.64.127.98
 58.18.221.37
 58.18.221.37
 89.165.3.1
 89.165.3.1
 89.165.3.1
 89.165.3.1
 89.165.3.1
 89.165.3.1

I would like to select only one of each group. I can't seem to find any way to do that because I do not have a header or a column name. All the methods that I find points to a select statement with a header.

I have tried using this approach:

$SQLFailedIPcsv =  $SQLFailedIPcsv|sort-object | Get-Unique –AsString

But that do not seem to work. Any idea on how I can select only one of each of these IPs?

Answer 1

You could import the file with Import-Csv and create a header with -Header IP. This will give your IP address data a header with the name IP.

To get unique IP addresses, we can pipe this to Select-Object, expand the column property with -ExpandProperty, then get unique items with -Unique.

Import-Csv -Path .\ip.csv -Header IP | Select-Object -ExpandProperty IP -Unique

Which will give the unique IP addresses:

112.64.127.98
58.18.221.37
89.165.3.1

If we wanted to retain the IP column header, we can use -Property instead:

Import-Csv -Path .\ip.csv -Header IP | Select-Object -Property IP -Unique

Which will give:

IP
--
112.64.127.98
58.18.221.37
89.165.3.1

---

Update

If we want to use foreach enumeration, we could wrap the it in a script block and use the Call operator &. Then we can pass it down the pipeline to Select-Object -Unique.

$csv = Import-Csv -Path .\ip.csv -Header IP

& {
    foreach ($row in $csv) {
        $row.IP
    }
} | Select-Object -Unique

# 112.64.127.98
# 58.18.221.37
# 89.165.3.1