How to Prefix a column values with an apostrophe ( ' )?

Question

I have a CSV file with multiple columns and 1000's of records, I need to prefix all the values of one of the columns (lets say 2nd column) with an apostrophe ' except in the first line or header line, There might be an easy one liner for this. How could I achieve this using awk or sed? Please note, I might have multiple commas in the values which are enclosed in double quotes.

Sample data:

"col1","col2","col3","col4","col5"
"value11","value12","value13","value14","value15"
"value21","value22","value23","value24","value25"
"value31","value32","value33","value34","value35"

Expected output:

"col1","col2","col3","col4","col5"
"value11","'value12","value13","value14","value15"
"value21","'value22","value23","value24","value25"
"value31","'value32","value33","value34","value35"

Answer 1

sed:

sed '2,$s/^\("[^"]*","\)/\1'"'"/ test.in

Using EREs to get rid of some of the escaping:

sed -E '2,$s/^("[^"]*",")/\1'"'"/ test.in

awk:

awk -F, 'NR>1{sub(/^"/,"\"'"'"'",$2)}1' test.in

If you don't want to worry about the quoting, use the escape code:

awk -F, '{sub(/^"/,"\"\x27",$2)}1' test.in

Answer 2

Using Perl:

perl -pi -e '
             BEGIN{
                 $column_number = 2; # Change as needed
                 $column_number--;
                 $apostrophe = chr 39;
             }
             next unless $this_is_data++; # Skip the first line
             s@ ^((?:"[^"]+"\s*,){$column_number}) "@$1"$apostrophe@x
           ' your_file

This assumes your fields don't contain backslash-escaped quotes.

Answer 3

Here's a gawk one:

$ gawk -F'","' -v var="'" -v OFS='","' 'NR>1{$2=var$2;} 1' foo.csv 

The -v option lets you define variables that are accessible to the gawk script. In this case, var is ' and OFS (the output field separator) is ",", same as the input field separator (-F). We then check that this is not the first line (NR>1) and add the value of var to the second column. Finally, the 1 is just a trick, it evaluates to true which makes gawk print the line. It is equivalent to adding a print; but shorter.

If you want to run this on a different column, just change $2=var$2; to $N=var$N where N is the column number you are interested in.

---

You can also do this in perl (naturally, you can do everything in perl):

$ perl -F'\",\"' -ane '$.>1 && do{$F[1]=chr(39).$F[1]}; 
                       print join("\",\"",@F)' foo.csv

The -a switch makes perl split input lines like gawk only that it saves them in the array @F (perl arrays start from 0, so the 2nd column will be $F[1], the 3rd $F[2] etc.). The -F (again like gawk) sets the input field separator. So, we check if the line number is greater than one ($.>1) and if it is, add the value of chr 39 (a ', thanks @josephR), to it. Finally we use join to connect each element in the array @F with "," and print the resulting string.

Answer 4

A simple sed will do:

$ sed 's/","/","\x27/' afile
"col1","'col2","col3","col4","col5"
"value11","'value12","value13","value14","value15"
"value21","'value22","value23","value24","value25"
"value31","'value32","value33","value34","value35"

Details

We're searching for the first occurrence of "," and replacing it with ","`. However escaping of the backtick can be tricky. So just put its hex escape code equivalent, \x27.

Your problem

This can be adapted like so to limit the changes to just rows you want.

$ cat <(head -n +1 afile) <(tail -n +2 afile | sed 's/","/","\x27/')
"col1","col2","col3","col4","col5"
"value11","'value12","value13","value14","value15"
"value21","'value22","value23","value24","value25"
"value31","'value32","value33","value34","value35"

Or you can skip the first line entirely, using sed if you know the trick 8-):

$ sed '2,$s/","/","\x27/' afile
"col1","col2","col3","col4","col5"
"value11","'value12","value13","value14","value15"
"value21","'value22","value23","value24","value25"
"value31","'value32","value33","value34","value35"

This tells sed to take only the 2nd line till the last line ($) and run those through the search and replace.