1

I want to add quotes in all the fields of the CSV file.

My CSV file

$ cat file.csv
1,563,45645,example-text,/example/path,FILE,ftp://,11

Expected output

$ cat file.csv
"1","563","45645","example-text","/example/path","FILE","ftp://","11"
oguz ismail
  • 1
  • 16
  • 47
  • 69
smc
  • 2,175
  • 3
  • 17
  • 30
  • 3
    please add your own efforts too in future... easiest would be `sed 's/[^,]*/"&"/g'` – Sundeep Apr 03 '19 at 10:58
  • None of these answers are particularly robust in that they'll break if given input where some fields are already quoted, so here's a perl one-liner using the Text::CSV_XS module: `perl -MText::CSV_XS -e 'my $csv = Text::CSV_XS->new({always_quote=>1});while (my $r = $csv->getline(\*STDIN)) { $csv->say(\*STDOUT, $r) }' < file.csv` in case that's a possibility – Shawn Apr 03 '19 at 12:16
  • @Shawn your statement is incorrect. My answer does handle that. The Perl is nice! – kvantour Apr 03 '19 at 12:28
  • @kvantour Your awk one doesn't handle quoted fields with commas in them. Unless you **know** your input doesn't have stuff like that (Which very well might be the case for OP's data), naive splitting on commas is not the way to handle CSV data. – Shawn Apr 03 '19 at 12:34
  • @Shawn valid point! – kvantour Apr 03 '19 at 12:47
  • the python solution mentioned in the other question linked here is useful because it handles the "blah,blah" case too – vicarage Dec 30 '22 at 09:18

3 Answers3

2

try this:

sed "s/,/\",\"/g;s/\(.*\)/\"\1\"/" file.csv

explanation:

s/           # substitute
,/           # all ,
\",\"        # with ","
/g           # global on whole line
;            # next expression
s/           # substitute
\(.*\)/      # save all into arg1 (\1)
\"\1\"/      # change to "\1"
UtLox
  • 3,744
  • 2
  • 10
  • 13
  • The last part can be reduced to `s/^\|$/"/g` and if you use single quotes you can get rid of all the back-slashes: `'s/,/","/g;s/^\|$/"/g'` – kvantour Apr 03 '19 at 11:51
1

There are many simple and direct ways to format your CSV file the way you want. However, if you want your CSV file to be RFC 1410 compliant you have to be a bit more careful. Especially with rule 7:

  1. If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote. For example:
    "aaa","b""bb","ccc"

source: RFC 1410: Common Format and MIME Type for CSV Files

This gives the following awk solution:

awk 'BEGIN{FS=OFS=","}
     { 
       for(i=1;i<=NF;++i) { 
         gsub("\042","\042\042",$i)
         sub("^\042\042","",$i)
         sub("\042\042$","",$i)
         $i="\042" $i "\042"
       }
     }1' file.csv

If, in addition, you want to be compliant with rule 1:

  1. Each record is located on a separate line, delimited by a line break (CRLF). For example:
    aaa,bbb,ccc CRLF

    zzz,yyy,xxx CRLF

source: RFC 1410: Common Format and MIME Type for CSV Files

awk 'BEGIN{FS=OFS=","; ORS="\r\n"}
     { 
       sub("\r$","")
       for(i=1;i<=NF;++i) { 
         gsub("\042","\042\042",$i)
         sub("^\042\042","",$i)
         sub("\042\042$","",$i)
         $i="\042" $i "\042"
       }
     }1' file.csv
Community
  • 1
  • 1
kvantour
  • 25,269
  • 4
  • 47
  • 72
1

Try Perl

$ cat smc.txt
1,563,45645,example-text,/example/path,FILE,ftp://,11
$ perl -lpe ' s/([^,]+)/"$1"/g ' smc.txt
"1","563","45645","example-text","/example/path","FILE","ftp://","11"
$

or using the lookarounds

$ perl -lne  ' s/^|(?<=,)|(?=,)|$/"/g  ; print ' smc.txt
"1","563","45645","example-text","/example/path","FILE","ftp://","11"
$
stack0114106
  • 8,534
  • 3
  • 13
  • 38