Shell Programming and Scripting

i have data as below

Code:

123,"paul phiri",paul@yahoo.com,"po.box 23, BT","Eco Bank,Blantyre,Malawi"

i need an output to be

Code:

123,"paul phiri",paul@yahoo.com,"po.box 23 BT","Eco Bank Blantyre Malawi"

And how are we supposed to guess which commas inside double quotes are unnecessary? And for those that you deem unnecessary, how are we supposed to guess which ones are to be removed and which ones are to be replaced by a space?

Code:

#!/usr/bin/perl

use strict;
use warnings;

while (my $record = <>) {
    my @fields = $record =~ /(".*?")/g;
        for my $field (@fields) {
            my $cache = $field;
            if ($cache =~ tr/,/ /) {
                $record =~ s/$field/$cache/;
            }
    }
    print $record;
}

Example:
Save as fix_csv.pl

Run as perl fix_csv.pl file.csv

Try:

Code:

awk '{for(i=2; i<=NF; i+=2) gsub(/, ?/," ",$i)}1' FS=\" OFS=\" file

or

Code:

awk '!(NR%2){gsub(/, ?/," ")}1' RS=\" ORS=\" file

thanks can u explain the 2nd code
awk '!(NR%2){gsub(/, ?/," ")}1' RS=\" ORS=\" file

---------- Post updated at 03:51 AM ---------- Previous update was at 03:48 AM ----------

Set the input record separator (RS) and the output record separator (ORS) to the double quote character: RS=\" ORS=\". After doing this, odd numbered records (1, 3, 5, ...) will be data outside of double quotes and even records (2, 4, 6, ...) will be data between double quotes.

For every field that is not (!) an odd numbered record (record number modulo 2 (NR%2)), globally substitute (gsub()) for every occurrence of a comma followed by 0 or 1 space character (/, ?/), replace it with a single space character (" ").

For every record read (whether or not any changes were made), print the record (1). Each group of awk statements is of the form:

Code:

pattern { actions }

If pattern evaluates to a non-zero, non-empty string, the actions will be performed on that record. If no pattern is given, the specified actions will be performed on every record. If no actions (and surrounding braes) are present, the default action is to print that record.

And, finally, the script is to process the input file named "file" (file).