Home > Uncategorized > 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]

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: