1

I'm trying for many hours to clean my csv file using (AWK or SED)

here is how looks the csv file:

id,name,contact-type,contact
"1","toto corp","tel","+123456789"
"1","toto corp","fax","+198765432"
"1","toto corp","site","totocorp.com"
"2","Namek corp","tel","+14377678563"
"2","Namek corp","fax","+19867334565"
"2","Namek corp","site","Namekcorp.com"

and I would like to have this output:

id,name,tel,fax,site
"1","toto corp","+123456789","+198765432","totocorp.com"
"2","Namek corp","+14377678563","+19867334565","Namekcorp.com"

Thank you for the hand guys!

1

gawk solution:

awk 'BEGIN{ FS=OFS=","; print "id,name,tel,fax,site" }
     { gsub(/"/,"",$3); a[$1][$3]=$4; if(!a[$1]["name"]) a[$1]["name"]=$2; }
     END { for(i in a) print i,a[i]["name"],a[i]["tel"],a[i]["fax"],a[i]["site"] }' file

The output:

id,name,tel,fax,site
"1","toto corp","+123456789","+198765432","totocorp.com"
"2","Namek corp","+14377678563","+19867334565","Namekcorp.com"

  • a[$1][$3]=$4 - multidimensional array, using id field value as parent key and contact-type field values as keys for the nested array
1

Assuming that there are no commas inside double quoted csv fields, then we may do the following in a simple sed code:

sed -ne '
     s/,/&\n/2;s/\n[^,]*,//;h
   n;s/.*,//;H
   n;s/.*,//;H
   g;s/\n/,/gp
' yourfile.csv

Results

"1","toto corp","+123456789","+198765432","totocorp.com"
"2","Namek corp","+14377678563","+19867334565","Namekcorp.com"
1

Another one gnu awk:

$ awk -F, -v OFS="," 'NR==1{h=$0;next}{a[$1FS$2]=(NR==2?FS $4:a[$1FS$2] FS $4)} \
END{print h;for (k in a) print k a[k]}' file

#Output:    
id,name,contact-type,contact
"2","Namek corp","+14377678563","+19867334565","Namekcorp.com"
"1","toto corp","+123456789","+198765432","totocorp.com"

Due to the way that awk iterates through arrays, in the END section the array is not printed in sorted order.

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.