Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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"
share|improve this question

4 Answers 4

up vote 4 down vote accepted

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
share|improve this answer
    
+1 for the sheer 'imparsability' of the gawk one :) –  terdon Oct 23 '13 at 18:30
    
Yeah, I considered looking up the escape code, but it's good practice for the OP to be careful with quoting. –  Kevin Oct 23 '13 at 18:31
    
I've got the escape code in mine. –  slm Oct 23 '13 at 18:33
    
@slm Yeah, but quotes are more fun :) –  Kevin Oct 23 '13 at 18:36
    
@Kevin - I couldn't figure them out quickly so went with the escape. –  slm Oct 23 '13 at 18:36

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.

share|improve this answer
3  
I love Perl and use it daily but when these solutions get posted it's like watching a air craft carrier pulling up to a frigate battle 8-). –  slm Oct 23 '13 at 18:38
    
@slm All fixed now. –  Joseph R. Oct 23 '13 at 18:46
    
@slm Strange. I tested it on a file exactly as provided in the example and it worked. –  Joseph R. Oct 23 '13 at 18:49
    
I see the issue, you're writing the changes back out to the file. Works now. –  slm Oct 23 '13 at 18:51
    
@slm Thanks for the feedback :) –  Joseph R. Oct 23 '13 at 18:53

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.

share|improve this answer
    
Better than mine 8-( –  slm Oct 23 '13 at 18:32
    
@slm dunno, using the escape code is better practice I just couldn't be bothered to look it up and yours is shorter if you add the 2,$ as Kevin has. –  terdon Oct 23 '13 at 18:34
    
For the Perl solution, don't you mean -F'","' and join("\",\""... as you did in gawk? –  Joseph R. Oct 23 '13 at 18:52
    
@JosephR. no because that fails on the first field and it turns out the syntax is clearer this way. I also found I had to escape the " for -F and it just got too complex. This will fail if the OP has quotes within the fields (and he does based on his other questions) but hey, that was not mentioned here :). By the way, I shamelessly stole char 39 from you, hope you don't mind. –  terdon Oct 23 '13 at 18:57
1  
@JosephR. done, works for commas within fields now. –  terdon Oct 23 '13 at 19:04

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.

share|improve this answer
1  
Sed can skip the first line without help, sed '2,$s/...' –  Kevin Oct 23 '13 at 18:33
    
@Kevin - yeah I just saw in yours, didn't know that trick –  slm Oct 23 '13 at 18:34
    
@Kevin - hope you don't mind, I added an example using 2,$. –  slm Oct 23 '13 at 18:36
    
Of course not, I added your escape too –  Kevin Oct 23 '13 at 18:36
    
@Kevin - OK thanks! –  slm Oct 23 '13 at 18:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.