4

CSV files format Data like the following

"Pizza,wings,icecream","hopper","0",,"Masdfasdf","USA","381","10 AM","cap",,,"monster"

Using a comma as the delimiter such as below will result in fields with commas to be counted as delimiters, which is not what i want.

cat /path/to/file | cut -d , -f2

will return

wings

when i want

hopper

I think i have found a solution logically, but i am unsure how to implement it.

How would i make a delimiter that would say "If i am a comma, surrounded by either another comma or quotes, count me as a delimiter"?

Example

[tksmith@rifle ~]$ cat deleteme 

"Pizza,wings,icecream","hopper","0",,"Masdfasdf","USA","381","10 AM","cap",,,"monster"

[tksmith@rifle ~]$ cat deleteme | cut -d , -f2

wings
Improve this question
4
  • You can define all those options that you mentioned as your delimiter.
    – myradio
    Jun 28, 2018 at 20:06
  • I double checked myself and this is not my case, i added an example of the command i just did.
    – TrevorKS
    Jun 28, 2018 at 20:13
  • 4
    Why reinvent the wheel? there are dedicated CSV parsers such as perl's Text::CSV or csvtool that can do this out-of-the-box e.g. csvtool col 2 file.csv
    – steeldriver
    Jun 28, 2018 at 20:39
  • or csvkit e.g. csvcut -c2
    – Thor
    Apr 15, 2021 at 18:49

2 Answers 2

Reset to default
4

CSV format has enough edge cases that you don't want to reinvent parsing it. Use a proper CSV parser. For example, with ruby

ruby -rcsv -e 'CSV.foreach(ARGV.shift) {|row| puts row[1]}' file
Improve this answer
1
  • perl's Text::CSV is more robust if the quoted fields can contain newlines.
    – glenn jackman
    Jun 28, 2018 at 22:07
2

The cut command command only delimits one character at a time. So you could transform the quotes and commas into another single character delimiter using sed and a regexp. For example:

sed -e 's/","\|",\|,"\|,,/@/g' /path/to/file | cut -d@ -f2

Will return

hopper

Another way is to use AWK. For example the following would give you the 2nd field.

awk -F ',,|^"|","|",|,"|"$' '{print $3}' /path/to/file

will return

hopper

The above examples aren't perfect as they don't handle all correctly all the cases with multiple empty fields and outside quotes. Best to use a higher level scripting language with a CSV parsing module.

Improve this answer
7
  • Using awk, he can get hopper with awk -F , '{print substr($4,2,6)}'. It does the same thing that your awk command does but it's a bit shorter.
    – Nasir Riley
    Jun 28, 2018 at 21:14
  • Nasir, the awk example in my attempts to delimit based on the the quoted text. Your awk example takes the fourth value after a comma and selects 6 characters starting at the 2nd position. The OP's question was how to get quoted values between delimiters -- the delimiters can be inside the quotes.
    – NoelProf
    Jun 28, 2018 at 21:40
  • In his example above, he said that he wanted hopper as the output. That is the output that your answer provides and the same thing that my command provides. My command does what you said but it gives the exact same output as yours so who cares? He can account for quoted values by using my example and printing a substring to get a value inside the quotes. He specified a certain output in his question so if he wants something else under different circumstances, he needs to modify the syntax. Bottom line, my command gives the same output as yours with less code so why not use the easier way?
    – Nasir Riley
    Jun 28, 2018 at 23:31
  • For his example, none of the commas, which he specifies as the delimiter, are inside of quotes but let's say that there was a part of the file where a delimiter was inside of quotes. Take the section of "10 AM" and let's say that the space inside is a delimiter and he wants AM as the output. Either awk '{print substr($2,1,2)}' deleteme or awk -F , '{print substr($10,5,2)}' deleteme will work. Whether the delimiter is inside the quotes or not, there's no need to add more work where it isn't necessary.
    – Nasir Riley
    Jun 28, 2018 at 23:50
  • 1
    Nasir, first of all his example does have commas inside the quotes. Second his question was: How would i make a delimiter that would say "If i am a comma, surrounded by either another comma or quotes, count me as a delimiter"? "
    – NoelProf
    Jun 29, 2018 at 21:16

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.