4

I have bad csv files, and need to add some quotes

In

field,field2,text field with potential commas,field4,field5
field,field2,text fie,ld with pot,ential commas,field4,field5
field,field2,text field with, potential commas,field4,field5

Out

field,field2,"text field with potential commas",field4,field5
field,field2,"text fie,ld with pot,ential commas",field4,field5
field,field2,"text field with, potential commas",field4,field5

sed 's/,/,"/2' will add the first quote, but how can I do the same with the second occurence backwards from the end, for each line?

sed, awk, perl and other methods are welcome. Files are some million lines, speed is appreciated.

5

Here's an awk way: if there are more than five comma-delimited fields, then loop through the "middle" fields concatenating them before printing the new field surrounded by quotes, followed by the final two fields:

awk -f awkscript.awk < input

With the following as the awkscript.awk:

BEGIN {
  OFS=","
  FS=","
}
{
        if (NF > 5) {
                middle=""
                for(i=3; i <= NF-2; i++)
                        middle=(middle ? middle"," : "")$i
                print $1, $2, "\""middle"\"", $(NF-1), $NF
        } else {
                print $1, $2, "\""$3"\"", $4, $5
        }
}
  • 1
    If you have the awk code in a file, I'd recommend you add FS="," to the BEGIN block, in case you forget to -F, -- or rather, so you don't need to remember it. – glenn jackman May 14 '18 at 21:11
  • That is exactly what I did, glenn. Also because it will run fine on my windows. (Windows doesn't like the -F parameter) – stiq May 14 '18 at 21:17
  • So incorporated! – Jeff Schaller May 14 '18 at 21:21
  • Info: this is ~200% faster than the sed solution, for my files. – stiq May 14 '18 at 21:40
  • Simpler: BEGIN{FS=OFS=","} {$3="\""$3;$(NF-2)=$(NF-2)"\"";print} (and also safe if any field 3 happens to be 0) – dave_thompson_085 May 15 '18 at 9:16
3

Using sed you would do:

sed 's/,/,"/2; s/\(,[^,]*,[^,]*\)$/"\1/' infile

This s/,/,"/2 replaces only second one. This s/\(,[^,]*,[^,]*\)$ matches ,anything-not-a-comma,anything-not-a-comma from the end $ of line as a group match with back-reference of \1, then in replacement part after adding a quote we bring it back "\1

3

Here is a stupidly simple approach, though probably not the most efficient:

sed 's/,/,"/2' input.csv | rev | sed 's/,/,"/2' | rev > output.csv
  • 1
    Brilliant. The only solution here I understand by glancing at. – user1717828 May 15 '18 at 2:56
2

perl: This version quotes all the fields, and handles any "interior" quotes

$ cat file
field,field2,text field with potential commas,field4,field5
field,field2,text fie,ld with pot,ential commas,field4,field5
field,field2,text field with, potential commas,field4,field5
field,field2,"Just drive," she said,f4,f5

$ perl -F, -lane '
    @result = ( splice(@F,0,2), undef, splice(@F,@F-2,2) );
    $result[2] = join ",", @F;
    print join ",", map {s/"/""/g; qq/"$_"/} @result
' file 

"field","field2","text field with potential commas","field4","field5"
"field","field2","text fie,ld with pot,ential commas","field4","field5"
"field","field2","text field with, potential commas","field4","field5"
"field","field2","""Just drive,"" she said","f4","f5"

The first line of code extracts the first 2 and last 2 data fields.
The second line collects all the rest of the data as the 3rd field.
The third line outputs as CSV.

0
sed -e '
   s/,/&\n/2
   s/\n\(.*\)\(,.*,.*\)/"\1"\2/
'    input.csv


 perl -pe '
    my $p;
    while ( /,/g ) {
       s/\G/"/,next if ++$p == 2;

       last if s/,\G(?=.*,)(?!.*,.*,)/",/;  # looks for 2nd last comma
    }
 '    input-file.csv



 perl -pe '
      substr($_, index($_, q/,/, 1+index($_, q/,/)), 1) = q/,"/;

      substr($_, rindex($_, q/,/, -1+rindex($_, q/,/)), 1) = q/",/;
 '     csvfile

Here we invoke the index function twice to get the position of second comma from the left. Then place a quote mark adjacent to it using the substr function now that the position has been ascertained.

Similarly, we invoke the rindex function and do the corresponding change.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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