CODEWITHSUNDEEP
bashsedawk
Deano
Deano

Reputation: 12230

How to add single quotes around columns using awk

Just wondering how can I add single quotes around fields, so I can import it to mysql without warnings or errors.

I have a csv file with lots of content.

16:47:11,3,r-4-VM,250000000.,0.50822578824,131072,0,0,0,0,0

Desired output 

'16:47:07','3','r-4-VM','230000000.','0.466028518635','131072','0','0','0','60','0'

I really have no idea where to start, your help is highly appreciated.

Upvotes: 5

Views: 11313

Answers (6)

Sara Farheen
Sara Farheen

Reputation: 1

awk 'BEGIN{FS=OFS=","}{for (i=1;i<=NF;++i)  $i="~"$i"~"}{print}' $input_csv_file

This works. Here I am enclosing all the csv file columns with a ~.

Upvotes: 0

Heath Raftery
Heath Raftery

Reputation: 4209

awk and sed are not going to (easily) determine whether the field separator (,) is escaped or not. The csv file format escapes , characters within fields by surrounding the whole field in double quotes (see Section 2.6 of RFC4180).

As I describe in this answer, a more robust method is to use a csv library, rather than parsing as text using regular expressions and the like.

I found Python's library was the best choice because it's:

  1. widely available without onerous dependencies, with the exception of Python itself;
  2. not particular sensitive to the version of Python you use;
  3. lends itself to being embedded within a shell script; and
  4. is quite compact (a one-liner will do!).

Based on the question's tags, I suspect these criteria will be appealing to you too.

Thus, try the following:

QUOTE_CSV_PY="import sys; import csv; csv.writer(sys.stdout, quoting=csv.QUOTE_ALL, quotechar=\"'\").writerows(csv.reader(sys.stdin))"
python -c "$QUOTE_CSV_PY" < file

To break it down:

  • QUOTE_CSV_PY is a shell variable containing the Python one-liner commands
  • The Python commands simply:
    • import the standard sys and csv modules;
    • create a csv writer that writes to standard output (stdout) with QUOTE_ALL set so all fields get quoted using quotechar, which is set to a single quote;
    • feed the csv writer a csv reader that reads from standard input (stdin).
  • The second line simply passes the one-liner to the python interpreter, and feeds the csv file (called file) into its stdin.

Upvotes: 2

potong
potong

Reputation: 58578

This might work for you (GNU sed):

sed -r 's/[^,]+/'\''&'\''/g' file

or:

sed -r "s/[^,]+/'&'/g" file

Upvotes: 1

Alper
Alper

Reputation: 13250

You could try this

awk -F"," -v quote="'" -v OFS="','" '$1=$1 {print quote $0 quote}' file
  1. Replace each separator (, comma) with (',' quote-comma-quote) -> (-F"," -v OFS="','")
  2. Add quotes to the begin and end of line -> (print quote $0 quote)

Upvotes: 20

Kent
Kent

Reputation: 195289

try this:

awk '{gsub(/^|$/,"\x027");gsub(/,/,"\x027,\x027")}7' file

example

kent$  echo "16:47:11,3,r-4-VM,250000000.,0.50822578824,131072,0,0,0,0,0"|awk '{gsub(/^|$/,"\x027");gsub(/,/,"\x027,\x027")}7'
'16:47:11','3','r-4-VM','250000000.','0.50822578824','131072','0','0','0','0','0'

Upvotes: 1

steveha
steveha

Reputation: 76775

#!/usr/bin/awk -f

BEGIN { FS=OFS=","}

{
    for (i = 1; i <= NF; ++i)
        $i = "'" $i "'"
    print
}

At the beginning, set FS (the field separator) to a comma; also set OFS, the output field separator, to a comma.

For every input line, loop over all fields. NF is the number of fields parsed out of the current line. Set each field to its own value surrounded by single quotes.

When done updating the fields, print the modified line.

Upvotes: 1

Related Questions