Home > Powershell > Script of the Day – remove duplicate lines in a CSV file

Script of the Day – remove duplicate lines in a CSV file

Friday today, so we’ll keep it short and sweet.

Someone dropped me a CSV file recently and asked if there was a quick way to remove duplicates.

Easy . .

Sample source file:

Script:

$dest = @()
foreach ($row in (import-csv .\duplicates.csv)){if (!($dest -match $row)){$dest += $row}};
$dest

CDB_Cab                                   Computername                              FindIt_Cab
-------                                   ------------                              ----------
LAB SL07                               LAB9252                               Decommissioned
LAB SL07                               LAB9301                               SL 07
LAB SL07                               LAB9309                               SL 07
LAB SL07                               LAB9304                               SL 07
LAB SL07                               LAB9310                               SL 07
LAB SL07                               LAB9311                               SL 07
LAB SL07                               LAB9312                               SL 07

Pretty basic stuff, but he was getting caught up in the portion of the script that matches a row to a table.
thing to remember is that

if (!($dest -match $row))

is not the same as

if ($dest -notmatch $row)

We fixed that, but afterwards I showed him that there was perhaps a quicker, easier way:

</pre>
gc .\duplicates.csv | Get-Unique
"CDB_Cab","Computername","FindIt_Cab"
"LONDON SL07","loncmss9252","Decommissioned"
"LONDON SL07","loncmss9301","SL 07"
"LONDON SL07","loncmss9309","SL 07"
"LONDON SL07","loncmss9304","SL 07"
"LONDON SL07","loncmss9310","SL 07"
"LONDON SL07","loncmss9311","SL 07"
"LONDON SL07","loncmss9312","SL 07"
if (!($dest -match $row))

Just depends on the format of your file.

Interestingly, you’d assume that if ‘get-unique’ worked on the import using gc, it would work on an import using import-csv, but alas:

</pre>
PS:9 >import-csv .\duplicates.csv | get-Unique

CDB_Cab                                   Computername                              FindIt_Cab
-------                                   ------------                              ----------
LONDON SL07                               loncmss9252                               Decommissioned

Powershell can be a very strange beast sometimes

[tags Script of the day, Scripting, Powershell]
[category Scripting]

Categories: Powershell Tags:
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.