Merge lines from one file if pattern matches

Shell Programming and Scripting
1

I have one comma separated file (a.txt) with two or more records all matching except for the last column.
I would like to merge all matching lines into one and consolidate the last column, separated by ":". Does anyone know of a way to do this easily?

I've searched the forum but most talked about two files or other types of merge...

Ex. I have this file a.txt:

I need output like so:

Thank you.

Gianni

2

I only have a start, with awk, hoping it will point you to a good direction:

awk -F\, 'NR==FNR{a[$1FS$2FS$3FS$4FS$5]=a[$1FS$2FS$3FS$4FS$5]":"$6;next}END{for (i in a) print i, a}' data
3

Cool. I tried it and it seems to work fine except for the first space then ':' but it gives me what I need:

Thank you!
Gianni

4

This version makes use of the fact that the input is sorted:

awk -F, '{e=$NF;$NF=""}p!=$0{if(NR>1)print s;p=s=$0}{s=s":"e}END{print s}' OFS=',' infile

TEST1,000A,20,1,,:_ABC_:_DEF_:_GHI_
TEST2,000B,80,,A,:_GHI_:_JKL_
5

No space.

awk -F \, '{a[$1FS$2FS$3FS$4FS$5]=a[$1FS$2FS$3FS$4FS$5]":"$NF } END {for (i in a) print i""a}' data
6

Awesome. Thanks everyone!

7

One in perl,

perl -lne '/(.+,)(.+)/;$h{$1}.= ":$2"; END{ while (($k,$v)=each(%h)) { print "$k $v" ; }  }' file