Replace commas with pipes, but not commas enclosed in double quotes

Question

I have a recordset that looks like this:

"BOSW0001","Mr","Wayne","Boswell","Wayne,Jessica & Lyn","31 Baker St" "ELLI0007","Mrs","Bronwyn","Elliott","Bronwyn, Paul & Arianne","98A Dandaraga Rd" "KENN0001","Mr","Leigh","Kenning","Leigh & Beth,Cole","22 Lake St" 

I want to replace the comma ( , ) with the pipe ( | ) without replacing the comma inside

 "Leigh & Bethie,Coles" "Waynez,Jessy & Lyne" "Bronwynie, Paula & Arianne" 

How to do this using regex or other methods?

Answer 1

You do not do this with regex; You do this using your own CSV analyzer. Here's an example (untested) using Text :: CSV_XS is the best in the business.

 use strict; use warnings; use Text::CSV_XS; my $in_file = "whatever.csv"; my $out_file = "new.dat"; open my $fh, '<', $in_file or die "$in_file: $!"; open my $out_fh, '>', $out_file or die "$out_file: $!"; my $in_csv = Text::CSV_XS->new; my $out_csv = Text::CSV_XS->new( { sep_char => '|', eol => "\n" } ); while( my $row = $in_csv->getline( $fh ) ) { $out_csv->print( $out_fh, $row ); } 

Answer 2

Just for TIMTOWTDI, here is an example using the main module Text :: ParseWords .

 #!/usr/bin/env perl use strict; use warnings; use Text::ParseWords 'parse_line'; foreach my $line (<DATA>) { print join '|', parse_line(',', 1, $line); } __DATA__ "BOSW0001","Mr","Wayne","Boswell","Wayne,Jessica & Lyn","31 Baker St" "ELLI0007","Mrs","Bronwyn","Elliott","Bronwyn, Paul & Arianne","98A Dandaraga Rd" "KENN0001","Mr","Leigh","Kenning","Leigh & Beth,Cole","22 Lake St" 

Answer 3

How to use the context in which the comma appears (between double quotes):

 s/","/"|"/g