2

I have a bunch of data inside "" and separated by , - Example below of two lines

"stampthisandthat.com","GANDI SAS","3fdb1bcc220148658e7ea1a96aef1585-1184687@contact.gandi.net","whois.gandi.net","A.DNS.GANDI.NET|B.DNS.GANDI.NET|C.DNS.GANDI.NET|","16-feb-2012","28-feb-2013","16-feb-2014","2012-02-16 00:00:00 UTC","2013-02-28 00:00:00 UTC","2014-02-16 00:00:00 UTC","clientTransferProhibited","2013-11-12 08:00:00 UTC","3fdb1bcc220148658e7ea1a96aef1585-1184687@contact.gandi.net","Laura VOGT","","Gandi, 63-65 boulevard Massena","","","","(Gandi) Paris","","(Gandi) 75013","(Gandi) FR","33143730576","","33170377666","","3fdb1bcc220148658e7ea1a96aef1585-1184687@contact.gandi.net","Laura VOGT","","Gandi, 63-65 boulevard Massena","","","","(Gandi) Paris","","(Gandi) 75013","(Gandi) FR","33143730576","","33170377666",""|
"salochinbd.com","FASTDOMAIN, INC.","nick.bekeris@gmail.com","whois.fastdomain.com","NS1.IPAGE.COM|NS2.IPAGE.COM|","17-feb-2012","03-feb-2013","17-feb-2014","2012-02-17 00:00:00 UTC","2013-02-03 00:00:00 UTC","2014-02-17 00:00:00 UTC","ok","2013-11-12 08:00:00 UTC","nick.bekeris@gmail.com","","","","","","","Cedar Rapids","Iowa","52402","UNITED STATES","","","13192100679","","nick.bekeris@gmail.com","","","","","","","Cedar Rapids","Iowa","52402","UNITED STATES","","","13192100679",""|

How would I go about keeping only certain data? For example, how can I keep only the data inside the first, second and fifth "".

  • 3
    When asking this type of questions please post an example of desired/expected output. – don_crissti Jul 16 '15 at 20:06
3
cut -d\" -f2,4,10 <in | tr \" , >out

...would grab only the quoted bits for fields 1,2 and 5 and afterward ensure they get separated w/ commas.

Or, since it appears some commas can occur within the quotes...

 cut -d\" -f-5,10-11 <in | sed s/,$// >out

...or even...

cut -d\" -f-5,10 <in | paste -d\" - /dev/null >out

...might set that to rights.

The first prints:

stampthisandthat.com,GANDI SAS,A.DNS.GANDI.NET|B.DNS.GANDI.NET|C.DNS.GANDI.NET|
salochinbd.com,FASTDOMAIN, INC.,NS1.IPAGE.COM|NS2.IPAGE.COM|

...and the second, third...

"stampthisandthat.com","GANDI SAS","A.DNS.GANDI.NET|B.DNS.GANDI.NET|C.DNS.GANDI.NET|"
"salochinbd.com","FASTDOMAIN, INC.","NS1.IPAGE.COM|NS2.IPAGE.COM|"

Here is an example demonstrating how to do similar for fields 1,3,17,21,22,23,24 :

printf '"%s"\n' "$(seq -s\",\" 35)" |
cut -d\" -f-3,6-7,34-35,42-48       |
paste -d\" - /dev/null

"1","3","17","21","22","23","24"

...which pulls only those fields out a seq output which looks like:

"1","2","3",..."35"
  • How does the columns work? I can't figure out how 5,10-11 = that data. All the columns I need currently are 1,3,17,21,22,23,24 (based off the awk command below) – Teddy291 Jul 16 '15 at 20:34
  • @Teddy291 - the fields are split on ". The first field is a null field - because the first character is ". Field 2 is everything between the first and second " and cut prints it led by a ", 3 is ",", 4 is "GANDI SAS", and 5 is ",' again. The 10th field is "A.DNS..., which, if printed without the 11th doesn't get a " appended, but if printed w/ it, gets ", appended - which is why we either append a " w/ paste and drop field 11, or we include it and strip the trailing , w/ sed. cut - if it works - is almost definitely the fastest way to do this. – mikeserv Jul 16 '15 at 20:38
2

There's a little-known program named csvquote that makes it possible to use standard tools like cut, sed, and awk to handle CSV files. It works by mapping special characters inside quotes to some unprintable characters, and then mapping them back. With this program, it's as simple as this:

csvquote file.csv | cut -d , -f 1,2,5 | csvquote -u

Output:

"stampthisandthat.com","GANDI SAS","A.DNS.GANDI.NET|B.DNS.GANDI.NET|C.DNS.GANDI.NET|"
"salochinbd.com","FASTDOMAIN, INC.","NS1.IPAGE.COM|NS2.IPAGE.COM|"
1

awk -F',' '{print $1 $2 $5}' - Is that what you are looking for ?

  • 2
    Sorry but this doesn't work for me as some of the data has , in the fields. How else could I do it using awk? – Teddy291 Jul 16 '15 at 20:19
0

To get around your issue with included commas in the fields, change your field separator to quote+comma; assuming that your included commas in the fields are not at the very beginning and/or end of the field.

$ awk -F'(\",)' '{print $1 $2 $17}' test.txt

Just make sure you escape the quote and surround field separator in single quotes to protect from your shell.

NOTE This is on Fedora 20 using gawk, I believe.

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.