Solved

Eliminate Duplicate Rows in CSV with PowerShell

Posted on 2013-10-24
2
895 Views
Last Modified: 2013-10-24
I'm looking for a way to import a CSV file in PowerShell and remove all duplicate entries. I am *not* looking for unique entries. If they're in the CSV more than once, I want all mention of them gone. Using the below example, I want the script to return only people who have one job, and to completely skip over users that have 2 or more jobs.

Here's an example CSV:
ID,LastName,FirstName,JobCode,JobTitle,DeptCode,Dept
1001,Doe,John,100,Programmer,199,IT
1002,Smith,Janice,110,Technician,199,IT
1003,Jordan,Michael,150,Janitor,102,Housekeeping
1003,Jordan,Michael,123,Cook,101,Cafeteria
1004,Johnson,Abigail,142,Nurse,180,Employee Health

Open in new window

The desired resulting CSV is:
ID,LastName,FirstName,JobCode,JobTitle,DeptCode,Dept
1001,Doe,John,100,Programmer,199,IT
1002,Smith,Janice,110,Technician,199,IT
1004,Johnson,Abigail,142,Nurse,180,Employee Health

Open in new window

I'm stumped here, I don't even have a good guess. I'm no farther than "Import-CSV"

Any help would be much appreciated.
0
Comment
Question by:Cacophony777
2 Comments
 
LVL 39

Accepted Solution

by:
footech earned 500 total points
Comment Utility
$data = Import-Csv test.csv | Group id | Where {$_.Count -eq 1} | Select -expandProperty group

Open in new window

You can then output the data back to a .CSV or whatever.
0
 
LVL 1

Author Closing Comment

by:Cacophony777
Comment Utility
Nice! That was easier than I thought it would be... I've never used the Group-Object command before. Thanks!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Utilizing an array to gracefully append to a list of EmailAddresses
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now