Remove spurious commas

Question

An idiot customer is generating csv files but one field sometimes has extra commas in (a description field).

Is there a tidy regex to find these bad records and replace the extra commas with something else. A SED command line would be fine.

Example:

A,B,C,This is a description,D,E
F,G,H,This is a description with a comma (,) in it,D,E

I need a SED that can tell that there are too many commas in the line and remove the extra comma from field 4.

We do not have the luxury of telling stupid customer to change their code.

Added

I would not object to a solution that just removes one spurious comma that I have to run multiple times.

Answer 1

Solution 1: single-line, delete ,

Here you go with an SED one-liner:

sed -r 's/([^,],[^,],[^,],)(.*)(,.+,.+)/\1'"$(sed -r 's/([^,],[^,],[^,],)(.*)(,.+,.+)/\2/' <<< $myInput | sed 's/,//g')"'\3/' <<< $myInput

You have to replace <<< $myInput with whatever your actual input is.
As you're working with CSVs you may have to tweak (both occurences of) the regex to match on each line of your CSV sheet.
In case your first three and last two fields are bigger than one char replace [^,] with [^,]*.

Explanation:
We use this regex

/([^,],[^,],[^,],)(.*)(,.+,.+)/

which captures the first (F,G,H,), second (.*) and last part (,D,E) of the string for us.
The first and third capture group will be unchanged, while the second is going to be substitued.
For the substitution we call sed a second (and actually third) time. First we capture only the second group, second we replace every , with nothing (only in the capture group!).

Proof:

Of course, if there is no unwanted comma, nothing gets replaced:

---

## Solution 2: whole file, line-by-line, delete `,` ## If you want to specify only **a file** and the replacement should happen for each line of the file you can use

while read line; do sed -r 's/([^,],[^,],[^,],)(.*)(,.+,.+)/\1'"$(sed -r 's/([^,],[^,],[^,],)(.*)(,.+,.+)/\2/' <<< $line | sed 's/,//g')"'\3/' <<< $line; done < input.txt

where input.txt at the end is - obviously - your file.
I just use the SED-command from above within a while-loop which reads each line of the text. This is necessary because you have to keep track of the line you're reading, as you're calling sed two times on the same input.

---

## Solution 3: whole file, enclose field in `"` ## As [@Łukasz L.][4] pointed out in the comments to the OP, according to the [RFC1480][5], which describes the format for CSV-files it would be better to enclose fields which contain a comma in `"`. This is more simple than the other solutions:

sed -r 's/([^,],[^,],[^,],)(.*)(,.*,.*)/\1"\2"\3/' input.txt

Again we have the three capturing groups. This allows us to simply wrap the second group in "!

Answer 2

If the column count is fixed, we can try cut out first three and last two columns with lookaheads ?: and match the commas inside the rest of the line (which is the description). I've got something like that:

(?:^(?:[^,]*,){3})(?:(?:[^,]*(,))*[^,]*)(?:(?:,[^,]*){2}$)

The [^,]* is the field (without comma) so (?:^(?:[^,]*,){3}) will cut first 3 columns (including the following comma). The (?:(?:,[^,]*){2}$) will remove the last 2 columns, including trailing comma. (?:(?:[^,]*(,))*[^,]*) matches the inside.

In JavaScript, the whole expression returns the complete description (with commas) as first match, and the commans within it as second. It gives the possibility, depending on Regex engine, either espace and replace the description (if engine gives the ranges of matched expression) or target the (,) expression matching comma with replacement syntax.

I don't have the possibility now to run and test with sed, but that regex should be very near to the solution you need.

Answer 3

I tried to solve this with sed, but could not perform the replace inside a match group. Instead, I managed to do it with a ruby one-liner that can be run from the terminal:

cat your_file | ruby -ne '$_.scan(/^(\w+,\w+,\w+,)([^$]+)(,\w,\w)$/).each{|m|puts m[0]+m[1].gsub(",","")+m[2]}'

This assumes that there are always 6 columns and that the 4th is the one that may contain commas.

The code has been tested with ruby 1.8.7, 1.9.1 and 2.1.0.

Answer 4

My approach is to calculate which commas to remove based in the total number of columns and which column you have to modify. It accepts three arguments, the input file, the total number of columns and the weird column with extra commas.

Next perl script does some calculations. When exists extra commas there will be additional columns, so it get where are positioned those extra columns and join them.

#!/usr/bin/env perl 

use warnings;
use strict;
use Text::CSV_XS;

my (@columns);

open my $fh, '<', shift or die;
my ($total_columns, $weird_column) = (shift, shift);

my $csv = Text::CSV_XS->new or die;
while ( my $row = $csv->getline( $fh ) ) { 
    undef @columns;
    if ( @$row == $total_columns ) { 
        @columns = @$row;
        next;
    }   

    my $extra_columns = @$row - $total_columns;
    my $post_columns_index = $weird_column + $extra_columns;
    @columns = ( 
        @$row[0..($weird_column-2)], 
        join( '', @$row[($weird_column-1)..($post_columns_index-1)]),  
        @$row[$post_columns_index..$#$row] 
    );  
}
continue {
    $csv->print( \*STDOUT, \@columns );
    printf "\n";
}

Assuming an input file like:

A,B,C,This is a description,D,E
F,G,H,This is a description with a comma (,) in it,D,E
F,G,H,This is, a description with two commas (,) in it,D,E
F,G,H,This is, a description with, three commas (,) in it,D,E

Run it like:

perl script.pl infile 6 4

That yields:

A,B,C,"This is a description",D,E
F,G,H,"This is a description with a comma () in it",D,E
F,G,H,"This is a description with two commas () in it",D,E
F,G,H,"This is a description with three commas () in it",D,E       

Perhaps it could fail with edge cases (first and last field). I didn't test it in depth, but I hope you get the idea. I tried to do it as general as possible.