5

Using sed, I'd like to substitute every comma that is outside of double quotes for a pipe.

So that this line in .csv file:

John,Tonny,"345.3435,23",56th Street

Would be converted to:

John|Tonny|"345.3435,23"|56th Street

Could you help me with the regex for that?

  • 3
    Are you sure you're not allowed to have \" if a string in double quotes contains a double-quote? That would complicate matters a bit. – Ulrich Schwarz Jun 20 '18 at 8:59
  • 2
    @UlrichSchwarz If it's standard CSV, he wouldn't have \" but he would have "", as double quotes are escaped by doubling them so the format doesn't have another special character to escape. – JoL Jun 20 '18 at 15:28
10

If your sed supports the -E option (-r in some implementations):

sed -Ee :1 -e 's/^(([^",]|"[^"]*")*),/\1|/;t1' < file

The

:label
   s/pattern/replacement/
t label

Is a very common sed idiom. It keeps doing the same substitution in a loop as long as it's successful.

Here, we're substituting the leading part of the line made of 0 or more quoted strings or characters other that " and , (captured in \1) followed by a , with that \1 capture and a |, so on your sample that means:

  • John,Tonny,"345.3435,23",56th Street -> John|Tonny,"345.3435,23",56th Street
  • John|Tonny,"345.3435,23",56th Street -> John|Tonny|"345.3435,23",56th Street
  • John|Tonny|"345.3435,23",56th Street -> John|Tonny|"345.3435,23"|56th Street
  • and we stop here as the pattern doesn't match any more on that.

With perl, you could do it with one substitution with the g flag with:

perl -pe 's{("[^"]*"|[^",]+)|,}{$1 // "|"}ge'

Here, assuming quotes are balanced in the input, the pattern would match all the input, breaking it up in either:

  • quoted string
  • sequences of characters other than , or "
  • a comma

And only when the matched string is a comma (when $1 is not defined in the replacement part), replace it with a |.

  • 1
    is the loop needed here? sed -E 's/("[^"]+"|[^,]+),/\1|/g' works for me on GNU sed.. – Sundeep Jun 20 '18 at 9:24
  • 2
    @Sundeep, yours wouldn't work on an input like ,foo or ",foo" or "x",",y" – Stéphane Chazelas Jun 20 '18 at 10:36
  • 2
    @Sundeep, or "a,b" or a,"b,c" or a,,b – Stéphane Chazelas Jun 20 '18 at 10:59
  • @Stéphane Could you explain the meaning of :1 in your command? – Quentin Jun 20 '18 at 11:04
  • @Quentin, see edit. – Stéphane Chazelas Jun 20 '18 at 12:00
14

Using csvkit:

$ csvformat -D '|' file.csv
John|Tonny|345.3435,23|56th Street

The tools in csvkit knows how to handle the intricacies of CVS files, and here we're using csvformat to replace the delimiting commas with | correctly. The output fields will be quoted as needed.

Example:

$ cat file.csv
John,Tonny,"345.3435,23",56th Street
The | factory,Ltd.,"0,0",meep meep

$ csvformat -D '|' file.csv
John|Tonny|345.3435,23|56th Street
"The | factory"|Ltd.|0,0|meep meep
3

With perl

perl -MText::CSV -lne '
  BEGIN { $p = Text::CSV->new() } 
  print join "|", $p->fields() if $p->parse($_)
' file.csv
John|Tonny|345.3435,23|56th Street
0
gawk '{gsub(/,/,"|"); printf("%s", $0 RT)}' RS='"[^"]*"' input.txt

or the same, but another way:

gawk -F, '{$1=$1; printf("%s", $0 RT)}' RS='"[^"]*"' OFS='|' input.txt

Input

John,Tonny,"345.3435,23",56th Street
one,"two,three",four,five,"six,stub,seven",eight,nine
one,"two,three",four,five,"six,stub,seven",eight,nine,"ten,eleven,twelve"

Output

John|Tonny|"345.3435,23"|56th Street
one|"two,three"|four|five|"six,stub,seven"|eight|nine
one|"two,three"|four|five|"six,stub,seven"|eight|nine|"ten,eleven,twelve"
0

With Python and csv module:

import csv,sys

with open(sys.argv[1]) as csvfile:
    csvr = csv.reader(csvfile)
    for line in csvr:
        dup = map( lambda x: '"' + x + '"' if ',' in x else x, line )
        print('|'.join(dup))

Works as so:

$ python3 csvfile.py  input.csv 
John|Tonny|"345.3435,23"|56th Street
John|Doe|"123.456,25"|26th Street
Jane|Doe|"987.654,52"|15th Street

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.