[SOLVED] CSV file - Fetch column value which has commas

CJenR · 06-01-2016, 03:48 PM

I have been trying to retrieve column values from CSV files using shell script.

My CSV file record is as follows:
1234,some text,"some text, with comma",text,alphanumeric,"again with, comma",text

My code: awk -F"," '{if(NR == 3) print $3}' FileName

I used comma as the field separator, but it does not get me the third column data. Can someone please help me

smallpond · 06-01-2016, 04:48 PM

Sure it is. Your third field is:

Code:

"some text

If you don't want it to consider commas inside quotes you need to use a different tool than regex, or else you need to preprocess the file before passing it to awk.

Edit: The reason you can't use regular expressions is that further down in your file is going to be:

Code:

1234,some text,"some text, with \"excaped, quotes\" and commas",text,alphanumeric,"again with, comma",text

rknichols · 06-01-2016, 04:53 PM

That turns out to be a non-trivial task, as you have found. Here is a function taken from an awk script that I wrote. I believe it handles all cases of quoted strings, escaped literal quote characters, embedded quoted newlines, etc., properly. It might need a llittle tweaking to work outside of the complete script where I use it. In particular, I notice that the variable stderr is never set to the value "/dev/stderr".

Code:

####
# parser(string,delim): Parse a CSV string with a given delimiter
# into a global fields[] array and return the number of fields found.
#
# The delimiter defaults to "," if not specified. Any leading or
# trailing spaces are conidered part of the field.
#
# Released to the public domain 1 June 2016 by Robert Nichols.
####
function parser(s,delim,   nc,aa,n,argnum,val,quoted) {
    argnum = 0; val = ""; quoted = 0
    if(delim == "")  delim=","
    nc = split(s, aa, "")
    for(n = 1; n <= nc+1; ++n) {
        if(n > nc || aa[n] == delim) {
            if(quoted) {
                if(n > nc) {    # quoted newline
                    val = val "\n"
                    if((getline s) < 1) {
                        print "EOF with unterminated quote" >stderr
                        fields[++argnum] = val
                        Errflg = 1
                        break
                    }
                    nc = split(s, aa, "")
                    n = 0
                    continue
                }
            }
            else {
                fields[++argnum] = val
                val = ""
                continue
            }
        }
        else if(aa[n] == "\"") {
            if(!quoted) { ++quoted; continue }
            if(aa[n+1] == "\"")  ++n    # literal quote char
            else { quoted = 0; continue }
        }
        val = val aa[n]
    }
    return argnum
}

CJenR · 06-01-2016, 05:46 PM

Yes, I want it to consider commas inside quotes.

@rknichols: I will look into your code. But just checking if there are simpler ways to do it.

Is it possible to preprocess it line by line and apply awk? or I need to preprocess the whole file (if so, Can I convert my existing csv file's field separator to be pipe (|) instead of comma). I have some 4000 records in the file so which is better?

I tried to use gsub:

Code:

awk -F '"' '{NF>1{ for(i=1;i<=NF;i+=2){gsub(",","|",$i)}}NF==1{gsub(",","|")}1' OFS='"' FileName

This gives me the expected file data as pipe separated but I want to know if I can get specific column data for each row using awk.

keefaz · 06-01-2016, 06:04 PM

If using gnu awk:
http://www.gnu.org/software/gawk/man...y-Content.html

allend · 06-01-2016, 06:05 PM

Have you tried using the FPAT variable? http://www.gnu.org/software/gawk/man...ing-By-Content

Edit: Too slow!

rknichols · 06-01-2016, 09:10 PM

Quote:

Originally Posted by CJenR

Yes, I want it to consider commas inside quotes.

@rknichols: I will look into your code. But just checking if there are simpler ways to do it.

Is it possible to preprocess it line by line and apply awk? or I need to preprocess the whole file (if so, Can I convert my existing csv file's field separator to be pipe (|) instead of comma). I have some 4000 records in the file so which is better?

You can process line by line as long as there are no embedded newline characters in the quoted strings. Since awk already works line by line, that will be no easier or harder than processing the whole file. Changing the field separator without affecting the commas in quoted strings will be just as hard as parsing the file as is. The problem is the quoted strings, which could potentially contain anything, including literal double quote chatracters.

Quote:

I tried to use gsub:

Code:

awk -F '"' '{NF>1{ for(i=1;i<=NF;i+=2){gsub(",","|",$i)}}NF==1{gsub(",","|")}1' OFS='"' FileName

This gives me the expected file data as pipe separated but I want to know if I can get specific column data for each row using awk.

That will fail if there are any literal double quote characters (which are represented in a CSV file by repeating that character) in a quoted string, as in this line, which has 4 fields:

Code:

123,"Tuesday","Amy said, ""Hello""",456

[EDIT]I see now that it does work if I take out that leading "{" character that was causing a syntax error and prevented me from trying it. As long as there are no embedded pipe characters in the quoted strings, it's fine. If there are, you are back to square 1.

There are lots of simpler methods if you aren't trying make a general solution that works for any valid CSV file.

syg00 · 06-01-2016, 09:27 PM

Well done - I gave up after the last time we broached this. For simple, well-defined cases FPAT works fine, but that function looks the goods ...

HMW · 06-02-2016, 06:12 AM

I am most likely missing something (it is HOT today!), but wouldn't this also work?

Code:

echo '1234,some text,"some text, with comma",text,alphanumeric,"again with, comma",text' | sed -r 's/,([^ ])/|\1/g' | awk -F "|" '{ print $3 }'
"some text, with comma"
echo '1234,some text,"some text, with comma",text,alphanumeric,"again with, comma",text' | sed -r 's/,([^ ])/|\1/g' | awk -F "|" '{ print $6 }'
"again with, comma"

Seems to work with another test case:

Code:

echo '123,"Tuesday","Amy said, ""Hello""",456' | sed -r 's/,([^ ])/|\1/g' | awk -F "|" '{ print $3 }'
"Amy said, ""Hello"""

This was an interesting thing to try to solve, happy for any pointers as to where my regex might fail.

Best regards,
HMW

rknichols · 06-02-2016, 08:21 AM

Quote:

Originally Posted by HMW

Code:

echo '1234,some text,"some text, with comma",text,alphanumeric,"again with, comma",text' | sed -r 's/,([^ ])/|\1/g' | awk -F "|" '{ print $3 }'
"some text, with comma"
echo '1234,some text,"some text, with comma",text,alphanumeric,"again with, comma",text' | sed -r 's/,([^ ])/|\1/g' | awk -F "|" '{ print $6 }'
"again with, comma"

That's yet another limited case, where the embedded comma is always followed by a space. Try it with

Code:

echo '123,"f(x,y)",456'

HMW · 06-02-2016, 08:33 AM

Quote:

Originally Posted by rknichols

That's yet another limited case, where the embedded comma is always followed by a space. Try it with

Code:

echo '123,"f(x,y)",456'

Yes, you're right. It fails on this. I knew it was too easy, dang!

Best regards,
HMW

rknichols · 06-02-2016, 08:56 AM

Also, ir is legal for a field delimiter comma to be preceded or followed by a space.

Code:

123, xyz ,456

Whether that white space is to be considered part of the field is not universally defined. The function I posted does include the white space in the field. CSV can look simple, but the full syntax not quite that easy to parse.

grail · 06-02-2016, 09:48 AM

Since others have mentioned it, here is the awk solution:

Code:

awk '{print $3"|"$6}' FPAT="([^,]+)|(\"[^\"]+\")" file

rknichols · 06-02-2016, 10:19 AM

Quote:

Originally Posted by grail

Since others have mentioned it, here is the awk solution:

Code:

awk '{print $3"|"$6}' FPAT="([^,]+)|(\"[^\"]+\")" file

Which works for simple CSV files, but fails if there are embedded literal quote characters, as in

Code:

111,222,"Amy said, ""Hello""",444,555,666

grail · 06-02-2016, 11:09 AM

hmmm .. true, I guess at the end of the day though we can all come up with something that a given solution will fail on