topic Re: awk help in Operating System - Linux

awk help

viseshu — Wed, 06 Dec 2006 00:54:11 GMT

i have a csv file like
"ab",12345,456,"home ahi","new one","get that me",45678,"new",123,"mnw",45,"mnuens"
"ab",12345,456,"home ahi","new one","get that me",45678,"new",123,"mnw",45,"mnuens","kil je"

I want the fields where a character fields contains double quotes" (in between the field not the " which encloses the field)

Please help me out.

Re: awk help

Dennis Handly — Mon, 24 Oct 2011 00:55:37 GMT

I don't see any entry that contains embedded double quotes??

This script assume that comma isn't embedded. It also assume that double quote isn't embedded in a non-quoted string.

#!/usr/bin/ksh
awk -F"," '
function check_field(i, ff) {
    len = length(ff)
    if (substr(ff, 1, 1) == "\"" && substr(ff, len, 1) == "\"") {
       if (index(substr(ff,2,len-2), "\"") != 0) {
          print "Found embedded double quote"
          print "record:", NR, " field:", i, ":" ff ":"
       }
    }
}
{
# check each field for embedded double quotes
for (i=1; i <= NF; ++i) {
    check_field(i, $i)
}
}' itrc_cvs.in

Re: awk help

viseshu — Wed, 06 Dec 2006 03:16:09 GMT

Dennis, it may happen like a double quote may be missing at the start or end of the field.. Then will it works fine????

Re: awk help

Dennis Handly — Mon, 24 Oct 2011 00:56:53 GMT

No, try this version. Note: it doesn't check for quotes being balanced.
#!/usr/bin/ksh
awk -F"," '
function check_field(i, ff) {
    len = length(ff)
    # strip off any double quotes
    if (substr(ff, len, 1) == "\"")
       --len
       start=1
       if (substr(ff, 1, 1) == "\"") {
          start=2
          --len
       }
       if (index(substr(ff,start,len), "\"") != 0) {
          print "Found embedded double quote"
          print "record:", NR, " field:", i, ":" ff ":"
       }
   }
{
# check each field for embedded double quotes
for (i=1; i <= NF; ++i) {
    check_field(i, $i)
}
}' itrc_cvs.in

Re: awk help

Peter Nikitka — Wed, 06 Dec 2006 09:30:31 GMT

Hi,

I would divide this request into two parts:
1) check for balanced quoting
2) generate the requested output

Solution 1)
awk -F, -v qu='"' '{for(i=1;i<=NF;i++) {if(! index($i,qu)) continue
m=match($i,qu".*"qu)
if(m) { if(length($i)==RLENGTH) continue
printf("Err: line=%d f=%d data outside quotes:%s:\n",NR,i,$i)}
else printf("Err: line=%d f=%d unbalanced quotes:%s:\n",NR,i,$i)}}'

Feeding this data
1234,aa","aa bb","dd"hh,"bb,456
would create
Err: line=1 f=2 unbalanced quotes:aa":
Err: line=1 f=4 data outside quotes:"dd"hh:
Err: line=1 f=5 unbalanced quotes:"bb:

Solution 2)
awk -F, -v qu='"' '{out=0; for(i=1;i<=NF;i++) {if(! index($i,qu)) continue
if (match($i,qu".*"qu) && (length($i)==RLENGTH)) {if(out) printf(FS);out++;printf(substr($i,2,RLENGTH-2))}}
if(out) printf("\n")}' above.csv

would create this output (with above data)
ab,home ahi,new one,get that me,new,mnw,mnuens
ab,home ahi,new one,get that me,new,mnw,mnuens,kil je

mfG Peter

Re: awk help

Sandman! — Wed, 06 Dec 2006 11:28:57 GMT

Another way to do it would to be to count the number of times double-quotes appear in the fields. If the count is other than zero (numeric field) or two (field punctuated by double-quotes) then print that field. If the field is zero then do nothing. If count==2, print the field if quotes aren't at the ends.

See the awk script below...

awk -F, '{
for (i=1;i<=NF;++i) {
f=$i
s=gsub("\"","",f)
if (s) {
if (s==2) {
if ($i!~/^".*"$/)
print $i
}
else
print $i
}
}
}' file

Re: awk help

john korterman — Wed, 06 Dec 2006 11:42:08 GMT

Hi,

a simple approach based on an idea similar to Sandman's:

#!/usr/bin/sh

typeset -i POS=0 NUMBER=0

while read LINE
do
while [ POS -lt ${#LINE} ]
do
POS=$POS+1
KARAK=$(echo ${LINE} | cut -c $POS)
if [ "$KARAK" = "\"" ]
then
NUMBER=$(( $NUMBER + 1 ))
KARAK=" "
fi
let DECISION=NUMBER%2
if [ "$DECISION" != 0 ]
then
echo "$KARAK\c"
fi
done
echo ""
POS=0
NUMBER=0
done <$1

Run it using your inputfile as $1

regards,
John K.

Re: awk help

viseshu — Wed, 06 Dec 2006 23:35:18 GMT

Is it possible to enclose the number fields with double quotes????(6,10,19 fields)
Presently double quotes are not enclosed with ""

Re: awk help

curt larson_1 — Thu, 07 Dec 2006 00:14:03 GMT

i know you asked for help using awk.

but, there are several perl modules written for handling csv files. Just a suggestion, use code that has already been written and tested instead of creating your own.

http://www.perlmeme.org/tutorials/parsing_csv.html

http://www.interopp.org/ncci/man/CSV.htm

http://search.cpan.org/search?query=csv&mode=all

Re: awk help

Sandman! — Thu, 07 Dec 2006 00:41:25 GMT

The numeric fields can have double-quotes and they will be treated the same as the non-numeric fields and irrespective of whether the field contains only double-quotes.

~hope it helps

Re: awk help

viseshu — Thu, 07 Dec 2006 01:04:41 GMT

Sandy,
Im generally asking this help... i need it in other script.Please help me out

Re: awk help

Peter Nikitka — Thu, 07 Dec 2006 02:42:49 GMT

Hi,

if you generally want to enclose fields with quotes and want to output all (valid) fields, modify my "Solution 2":
Solution 2b)
awk -F, -v qu='"' '{out=0; for(i=1;i<=NF;i++) {if(out) printf(FS);out++
if(! index($i,qu)) {printf("%s%s%s",qu,$i,qu); continue }
if (match($i,qu".*"qu) && (length($i)==RLENGTH)) printf($i)}
if(out) printf("\n")}' above.csv

mfG Peter

Re: awk help

Peter Nikitka — Thu, 07 Dec 2006 03:41:04 GMT

Hi,

reading your thread again: if you really don't care about the original placement of the quotes, you can use a 'forced' handling of your fields: drop all quotes at input and create new ones at output:

awk -F, -v qu='"' 'NF {gsub(qu,""); printf("%s%s%s",qu,$1,qu)
for(i=2;i<=NF;i++) printf("%s%s%s%s",FS,qu,$i,qu)
printf("\n")}' above.csv

mfG Peter