Shell Programming and Scripting

Hi ,

I have a CSV file that has around 50K records in it. I have to delete all the duplicate rows from the file except one, depending upon data in column4.
Lets say there are 3 rows for 'column4data' in the file. I have to retain only that line which has the smallest date value in column2.
The file is already sorted based on column4data.

e.g For the lines below :

Code:

           bcd,20090101,20100101,column4data ,4,5,6,7
           a_b,20080101,20090101,column4data ,1,2,3,4
           xyzz,20100101,20110101,column4data ,1,2,6,7

Result Expected ( Since 20080101 is least among all ):

Code:

           a_b,20080101,20090101,column4data ,1,2,3,4

The goal is to read every row of the file & read column4data in every row. Lets say column4data is unique ( does not occur twice) , move ahead and read column4data from second row and so on. Once we get a dulicate occurrence , read column2data from all the rows and do date comparsion and subsequent deletion.

Can somebody pls help me with a solution using 'awk' or 'sed' !

TIA

Try this,

Code:

awk -F"," '{if(! a[$4] ) {a[$4]=$2;b[++i]=$0} else if( $2 < a[$4]){a[$4]=$2;b[i]=$0}} END {for(j=1;j<=i;j++) {print b[j]}}' inputfile

Since the file is sorted on column 4:

Code:

awk -F, 'p!=$4{if(s)print s;min=$2;p=$4}min>=$2{s=$0;min=$2}END{print s}' infile

Code:

sort -t, -k4,4 -k2,2n infile |awk -F, '!a[$4]++'