1

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?

1
  • 4
    If your data is getting too complex to be parsed reliably by simple text processing tools like awk, you may want to look at other options such as a csvsql from the Python-based csvkit where you could do a SELECT...WHERE query, or Miller with its filter verb. Sep 19, 2020 at 13:52

2 Answers 2

5

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 9th field is always embedded in quotes.

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
3
  • Yeah this works, but my data isn't enclosed in quotes but somehow when i read or cat the file I see it like this. Sep 19, 2020 at 8:38
  • How can i order this field and print another column along as well.? Sep 19, 2020 at 8:39
  • The quotes are part of the CSV format. It is valid (but unusual) to quote every field. If your data itself had quotes, they would be doubled to avoid confusion, like ...,"Say ""Bang"" loudly",.... Sep 19, 2020 at 9:09

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.