Remove double quotes if delimiter value is not present in data

Question

An input file is given, each line of which contains quotes for each column and carriage return/ new line character.

• If the line contains new lines it has be appended with in the same line which is inside the quotes i.e for example line 1

• Removing of double quotes for each column if the delimiter(,) is not present.

• Removing of Carriage Return characters i.e(^M)

To exemplify, given the following input file

"name","address","age"^M
"ram","abcd,^M
def","10"^M
"abhi","xyz","25"^M
"ad","ram,John","35"^M

I would like to obtain the following output by means of a sed/perl/awk script/oneliner.

name,address,age
ram,"abcd,def",10
abhi,xyz,25
ad,"ram,John",35

Solutions which i have tired it so far For appending with previous line

sed '/^[^"]*"[^"]*$/{N;s/\n//}' sample.txt

for replacing control-m characters

perl -pne 's/\\r//g' sample.txt

But i didn't achieve final output what i required below

Answer 1

Use a library to parse CSV files. Apart from always wanting to use a library for that here you also have very specific reasons, with embedded newlines and delimiters.

In Perl a good library is Text::CSV (which wraps Text::CSV_XS if installed). A basic example

use warnings;
use strict;
use feature 'say';

use Text::CSV;

my $file = shift or die "Usage: $0 file.csv\n";

my $csv = Text::CSV->new({ binary => 1, auto_diag => 1 }); 

open my $fh, '<', $file  or die "Can't open $file: $!";

while (my $row = $csv->getline($fh)) { 
    s/\n+//g for @$row; 
    $csv->say(\*STDOUT, $row);
}

Comments

• The binary option in the constructor is what handles newlines embedded in data

• Once a line is read into the array reference $row I remove newlines in each field with a simplistic regex. By all means please improve this as/if needed

• The pruning of $row works as follows. In a foreach loop each element is really aliased by the loop variable, so if that gets changed the array changes. I used default where elements are aliased by $_, which the regex changes so $row changes.

  I like this compact shortcut because it has such a distinct look that I can tell from across the room that an array is being changed in place; so I consider it a sort-of-an-idiom. But if it is in fact confusing please by all means write out a full and proper loop

• The processed output is printed to STDOUT. Or, open an output file and pass that filehandle to say (or to print in older module versions) so the output goes directly to that file

The above prints, for the sample input provided in the question

name,address,age
ram,"abcd,def",10
abhi,xyz,25
ad,"ram,John",35

Answer 2

FPAT is the way to go using gnu awk, it handles comma separated files.

1. remove ^m
2. clean lines
3. remove qutes

.

dos2unix sample.txt  

awk '{printf "%s"(/,$/?"":"\n"),$0}' sample.txt > tmp && mv tmp sample.txt
"name","address","age"
"ram","abcd,def","10"
"abhi","xyz","25"
"ad","ram,John","35"


awk -v FPAT="([^,]+)|(\"[^\"]+\")" -v OFS=, '{for (i=1;i<=NF;i++) if($i!~",") $i=substr($i,2,length($i)-2)}1' sample.txt
name,address,age
ram,"abcd,def",10
abhi,xyz,25
ad,"ram,John",35

All in one go:

dos2unix sample.txt && awk '{printf "%s"(/,$/?"":"\n"),$0}' sample.txt | awk -v FPAT="([^,]+)|(\"[^\"]+\")" -v OFS=, '{for (i=1;i<=NF;i++) if($i!~",") $i=substr($i,2,length($i)-2)}1'

Normally you set Filed Separator FS or F to tell how filed are separated. FPAT="([^,]+)|(\"[^\"]+\")" FPAT tells how the filed looks like using a regex. This regex is complicated and often used with CSV.

• (i=1;i<=NF;i++) loop through on by one field on the line.
• if($i!~",") if it does not contain comma, then
• $i=substr($i,2,length($i)-2) remove first and last character, the "

If a field for some reason do not contain ", this is more robust:

awk -v FPAT="([^,]+)|(\"[^\"]+\")" -v OFS=, '{for (i=1;i<=NF;i++) if($i!~",") {n=split($i,a,"\"");$i=(n>1?a[2]:$i)}}1' file

It will not do any thing to a field not contains double quote.

Answer 3

This might work for you (GNU sed):

sed ':a;/[^"]$/{N;s/\n//;ba};s/"\([^",]*\)"/\1/g' file

The solution is in two parts:

1. Join broken lines to make whole ones.
2. Remove double quotes surrounding fields that do not contain commas.

If the current line does not end with double quotes, append the next line, remove the newline and repeat. Otherwise: remove double quotes surrounding fields that do not contain double quotes or commas.

N.B. Supposes that fields do not contain quoted double quotes. If that is the case, the condition for the first step would need to be amended and double quotes within fields would need to catered for.

Answer 4

With perl, please try the following:

perl -e '
while (<>) {
    s/\r$//;      # remove trailing CR code
    $str .= $_;
}

while ($str =~ /("(("")|[^"])*"\n?)|((^|(?<=,))[^,]*((?=,)|\n))/g) {
    $_ = $&;
    if (/,/) {    # the element contains ","
        s/\n//g;  # then remove newline(s) if any
    } else {      # otherwise remove surrounding double quotes
        s/^"//s; s/"$//s;
    }
    push(@ary, $_);

    if (/\n$/) {  # newline terminates the element
        print join(",", @ary);
        @ary = ();
    }
}' sample.txt

Output:

name,address,age
ram,"abcd,def",10
abhi,xyz,25
ad,"ram,John",35