CODEWITHSUNDEEP
regexbashcsvawksed
Stelios
Stelios

Reputation: 1102

Wrap a single field with single quotes using awk

There are a number of examples using awk to wrap fields with double quotes. I have unsuccessfully been trying to enclose a field with single quotes for data loading csv files in postgresql.

Below are a few of my attempts:

#!/usr/bin/awk -f
BEGIN { FS=OFS=","}

{
  for (i = 1; i <= NF; ++i)
    if($i == 9)
    {
        $i = "\'' $i \''"
    }

  print $0 >> "output.csv"
}

or

awk 'BEGIN { OFS=FS="," } { $9= ""'" $9 ""'"} 1' container.csv > output.csv

also...

awk -v q="'" 'BEGIN { FS="," } { sub($9, ""\'"&"\'"" );print}' container.csv > output.csv

Upvotes: 1

Views: 1509

Answers (4)

Sundeep
Sundeep

Reputation: 23667

Solution using sed

$ s='one,two,three,four,five,six,seven,eight,nine,ten'

$ # s///n means nth matching occurrence
$ echo "$s" | sed "s/[^,]*/'&'/9"
one,two,three,four,five,six,seven,eight,'nine',ten
$ # * used as quantifier so that it will work on empty fields too
$ echo 'a,c,,d' | sed "s/[^,]*/'&'/3"
a,c,'',d  

$ # or if hex escape sequences are allowed
$ # this is preferred as it avoids shell interpretation within double quotes
$ echo "$s" | sed 's/[^,]*/\x27&\x27/9'
one,two,three,four,five,six,seven,eight,'nine',ten

Upvotes: 1

Charles Duffy
Charles Duffy

Reputation: 295403

$ awk 'BEGIN { FS = OFS = "," } { $9= "'"'"'" $9 "'"'"'"; print }' \
>    <<<one,two,three,four,five,six,seven,eight,nine,ten
one,two,three,four,five,six,seven,eight,'nine',ten

The tricky thing here is getting your quotes through bash into awk -- if you have a single quote in your single-quoted command-line argument, it's treating as ending the quoting context that started at 'BEGIN, not as a literal thing to be sent to awk.

Thus, "'"'"'" does the necessary trickery:

  • The first character, ", is literal, passed to awk
  • The second character, ', is syntactic, used to tell the shell to end the quotes that began at the front of the command line
  • The third character, ", is syntactic, used to begin a new (double-quoted) quoting context.
  • The fourth character, ', is literal inside that context.
  • The fifth character, ", ends the double-quoting context started at character three
  • The sixth character, ', is syntactic, resuming the single-quoted context ended by character two
  • The seventh character, ", is literal, passed to awk.

Thus, what's actually passed to awk to be used as a script in the above is:

BEGIN { FS = OFS = "," } { $9= "'" $9 "'"; print }

...which you could just put directly in a file, if you preferred; if that awk script had a #!/usr/bin/awk -f shebang, it should work when directly executed as a command.

If your shell is bash, by the way, there's an alternate quoting context that will make this much less awful:

$ awk $'BEGIN { FS = OFS = "," } { $9= "\'" $9 "\'"; print }'

Inside of $'', backslash escapes are honored -- \t is a tab, \f is field separator, \r is a newline, and -- relevant to our point -- \' is a single quote.

Upvotes: 1

Ed Morton
Ed Morton

Reputation: 203522

Just use the octal escape sequence \047 wherever you need a single quote:

awk 'BEGIN{FS=OFS=","} { $9= "\047" $9 "\047"; print }'

That avoids any complexity in quoting and any surprises that result from other approachess.

Upvotes: 1

Tom Fenech
Tom Fenech

Reputation: 74615

This is much easier within a script, since you don't need to worry about the enclosing quotes:

BEGIN { FS = OFS = "," }

{ 
    $9 = "'" $9 "'"
    print
}

I'm not sure what your loop was supposed to be doing!

Testing using the input kindly provided by Charles:

$ cat file
one,two,three,four,five,six,seven,eight,nine,ten
$ awk -f script.awk file
one,two,three,four,five,six,seven,eight,'nine',ten

Upvotes: 1

Related Questions