how can I add the last column in flatfile by reading the other columns

[unixb1234]

Hello everyone,

The below is the flat file I have with 7 columns. This flatfile mainly consists f only 2 unique values under 'field_1" column.

I need to add the additional column at the end of this as "field_8" with the value assigned starting from "1" in increments by "1" for a particular field_1 value.

For example: for the field_1 value of "ab cde 111111111", the field_8 value should be 1,2 and 3 since this filed_1 repeats three times(or this value has three records).

Similarly, for the field_2 value of "bb ccc 222222222",
the field_8 value should be 1,2 , 3 and 4 since this filed_1 repeats four times(or this value has four records).

This last column field_8 should be after the comma',' and within " as below.


The flat file is as below:
******************************************
"field_1","field_2","field_3","field_4","field_5","field_6","field_7"

"ab cde 111111111","xyx","aaaaaaaaa","SSSSSSSSSA",20081013,20081013,"1000 "
"ab cde 111111111","xyx","aaaaaaaaa","SSSSSSSSSA",20081013,20081013,"2000 "
"ab cde 111111111","xyx","aaaaaaaaa","SSSSSSSSSA",20081013,20081013,"3000 "

"bb ccc 222222222","yyy","bbbbbbbbb","FFFFFFFFFA",20081013,20081013,"8001 "
"bb ccc 222222222","yyy","bbbbbbbbb","FFFFFFFFFA",20081013,20081013,"2345 "
"bb ccc 222222222","yyy","bbbbbbbbb","FFFFFFFFFA",20081013,20081013,"67890"
"bb ccc 222222222","yyy","bbbbbbbbb","FFFFFFFFFA",20081013,20081013,"8909 "

******************************************



The output flat file should be as below:
******************************************
"field_1","field_2","field_3","field_4","field_5","field_6","field_7","field_8"

"ab cde 111111111","xyx","aaaaaaaaa","SSSSSSSSSA",20081013,20081013,"1000 ","1"
"ab cde 111111111","xyx","aaaaaaaaa","SSSSSSSSSA",20081013,20081013,"2000 ","2"
"ab cde 111111111","xyx","aaaaaaaaa","SSSSSSSSSA",20081013,20081013,"3000 ","3"

"bb ccc 222222222","yyy","bbbbbbbbb","FFFFFFFFFA",20081013,20081013,"8001 ","1"
"bb ccc 222222222","yyy","bbbbbbbbb","FFFFFFFFFA",20081013,20081013,"2345 ","2"
"bb ccc 222222222","yyy","bbbbbbbbb","FFFFFFFFFA",20081013,20081013,"67890","3"
"bb ccc 222222222","yyy","bbbbbbbbb","FFFFFFFFFA",20081013,20081013,"8909 ","4"

******************************************


Is this possible in AWK ?

Please advise and thanks very much again in advance. I appreciate all the help regarding this.

Regards,
Bhanu

 

[Annihilannic]

Try this:

awk '
        BEGIN { FS=OFS="," }
        NR == 1 { print $0,"\"field_8\""; next }
        NF > 1 { print $0,"\""++count[$1,$2]"\""; next }
        1 # print any other blank lines
' inputfile > outputfile

The count array is indexed by the contents of the first two fields and incremented just before printing.

Annihilannic.

 

[LKBrwnDBA]

Maybe a teeny-weeny bit "Shorter"?

awk -F, 'NR == 1 { print $0,"\"field_8\""; next }
         NF  > 1 { print $0,"\""++c"\""; next }
         1 {print; c=0}
' inputfile > outputfile

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[unixb1234]

Thanks again, so very much to both of you Annihilannic (MIS) and LKBrwnDBA (MIS). It works great and thansk again and I really appreciate your help.
Regards,
Bhanu

 

[Annihilannic]

LKBrwnDBA, perhaps... assuming the file is sorted. Also using -F only sets the input field separator, hence my BEGIN clause. That solution won't put commas before the 8th field... only a minor tweak to fix of course.

Annihilannic.