How to separate columns when delimiter is included in the fields

Question

I have a CSV file with 24 fields. The record looks something like the sample below. So some fields have ',' in their value. How do I use the delimiter?

I'm trying to find the values greater than 200 in column 9. But I end up with text of the previous columns because of the delimiter issue.

"86680728811_10150499874478812","86680728811","fun ,celebrators.","New York City’s buildings, descend on Times Square when the iconic ball drops tomorrow...","abcnews.go.com","link","published_story","271","31","0","0","0","0","0","0","0","http://abcnews.go.com/blogs/headlines/2011/12/wishes-for-2012-to-fall-on-times-square/","https://external.xx.fbcdn.net/safe_image.php?d=AQAbTSWm1WlXInTf&w=130&h=130","2012-01-01 02:00:37"

How do I resolve this?

Answer 1

You may want to use a tool which knows how to parse CSVs. For instance, with miller:

mlr -N --csv filter '$9 > 200' < your-file

With GNU awk, you can set FPAT to define fields based on the patterns they match rather than the separator that delimits them:

gawk -v 'FPAT=[^",]*|"([^"]|"")*"' 'substr($9,2) > 200'

Here also handling " escaped as "", but assuming field values don't contain newline characters and that the 9^th field is always embedded in quotes.

Answer 2

If your requirements are

1. All your data is enclosed in double quotes as you example shows
2. You are not interested in the first and last fields (or are prepared to do some extra work to deal with them.

then you can view the delimiter as being the three character sequence ",".

awk -F '","' '$9>200 {print}' file.csv

(The {print} is not needed, I put it in for clarity to indicate where to add additional code).

Edit: expanded example, print 3 columns in the same format when the ninth is more than 200.

awk -F '","' 'BEGIN {OFS=FS}$9>200{print "\""$3,$8,$9"\""}'

Change the delimiter to | and sort in numeric order.

awk -F '","' 'BEGIN {OFS="|"}$9>200{print $3,$8,$9}' | sort -d'|'-n -k3