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
  • If I have such a data, I would convert them first into proper XML and I then will manipulate xml file with xmlstarlet or via some other XSL script. But I know it is my preference to have such date in XML database. Then you can export CSV file from XML easier then to combine lines of csv together. <items><item><name id="1">toto corp</name><tel>123</tel><fax>234</fax><whatever>hfjdgs</whatever></item></items>
    – xerostomus
    Commented Jul 5 at 18:22

4 Answers 4

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.

1

I'm aware that the original user is looking for a solution using awk or sed, but since these tools don't know about the quoting rules of CSV, it may be worth showing a solution with a tool that knows how to parse CSV.

Using the CSV-aware tool Miller (mlr) and its reshape operation to create new fields from the contact-type fields with values from the contact field:

$ mlr --csv reshape -s contact-type,contact file
id,name,tel,fax,site
1,toto corp,+123456789,+198765432,totocorp.com
2,Namek corp,+14377678563,+19867334565,Namekcorp.com

Again, but in fancy pretty-printed box format:

$ mlr --c2p --barred reshape -s contact-type,contact file
+----+------------+--------------+--------------+---------------+
| id | name       | tel          | fax          | site          |
+----+------------+--------------+--------------+---------------+
| 1  | toto corp  | +123456789   | +198765432   | totocorp.com  |
| 2  | Namek corp | +14377678563 | +19867334565 | Namekcorp.com |
+----+------------+--------------+--------------+---------------+

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .