3

I have a lot CSV files that I have combined. However, there are duplicates, but the entire line is not duplicated. I do have a column that I want to use as the criteria to search for a duplicate. And if there is a duplicate in that column from the entire column, then delete the rows that contain the duplicates in the columns until you have all unique values in this column.

Does anyone know the best way to accomplish this in Bash, sed or awk?

  • 1
    Could you please provide some sample data and expected output of it to help pinpoint what you are after? Either way it seams easier to accomplish with a script language. See e.g. Ruby's CSV class. – N.N. Mar 22 '13 at 17:36
5
awk -F, '!seen[$1]++'

$1 is the first column, change as appropriate; you can use multiple columns separated by commas ([$1,$3]), or $0 for the whole row.

  • Wow! This is amazingly simple! Thanks. This works perfectly. For any super noobs like me. You simply need to put the csv after the command above & if you want to output it, then use this >. It would look like this: awk -F, '!seen[$1]++' inputfile.csv > outputfile.csv. – Peaceful_Warrior Mar 23 '13 at 21:00
3

Bash is hard but you can you call Perl from Bash? If your fields are separated by commas, and your key field is the second one,

$ cat a.csv
11,22,33
214,22,354
6,6,6
4,5,7
1,22,1

this one liner will print if it hasn't seen the second column already:

$ perl -ne '$value = (split /,/)[1]; print unless $x{$value}++;' a.csv 
11,22,33
6,6,6
4,5,7

The [1] is the column number and is zero-based.

0

If there are fields that contain delimiter characters inside quotes (doesn't work with Ruby 1.8):

$ ruby -rcsv -e 'puts CSV.parse($<).uniq{|l|l[1]}.map(&:to_csv)' <<< $'"1,9",3\n4,8\n7,3'
"1,9",3
4,8

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.