Geek Answers Handbook

Removing extra commas from a comma-delimited file

I have a comma delimited file with 12 columns.

There is a problem with the 5th and 6th columns (the text in the 5th and 6th columns is identical, but may contain additional commas between them), which contains additional commas.

 2011,123456,1234567,12345678,Hey There,How are you,Hey There,How are you,882864309037,ABC   ABCD,LABACD,1.00000000,80.2500000,One Two

So, in the above example, “Hey, how are you,” there should not be a comma.

I need to remove extra commas in the 5th and 6th columns.

+4
source share
3 answers

If you always want to remove the 5th comma, try

sed 's/,//5' input.txt

, . , , .

, awk. , , - , :

awk -f script.awk input.txt

script.awk:

BEGIN{
    FS=","
}
NF<=12{
    print $0
}
NF>12{
    for (i=1; i<=4; i++) printf $i FS
    for (j=0; j<2; j++){
        for (i=0; i<=(NF-12)/2; i++){
            printf $(i+5)
            if (i<(NF-12)/2) printf "_"
            else printf FS
        }
    }
    for (i=NF-5; i<=NF; i++) printf $i FS
    printf "n"
}

,. 12, , . 12 , 4 ( ), 5 ( 6), , _. .

, , . , .

+4

, .

   sed -r 's/(,)[0-9]/;/g' a | sed -r 's/[0-9](,)/;/g' |  sed -r 's/,//g' |  awk -F\; '{ print $1 "," $2 "," $3 "," $4 "," substr($5, 0, length($5)/2) "," substr($5, length($5)/2 +1, length($5)/2) "," $6 "," $7}'
2011,23456,234567,234567,Hey ThereHow are you,Hey ThereHow are you,8286430903,
+2

perl Text::CSV_XS:

#!/usr/bin/env perl

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

my (@columns);

open my $fh, '<', shift or die;

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

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

, (infile) , , , :

2011,123456,1234567,12345678,Hey There,How are you,Hey There,How are you,882864309037,ABC   ABCD,LABACD,1.00000000,80.2500000,One Two
2011,123456,1234567,12345678,Hey There,How are you,now,Hey There,How are you,now,882864309037,ABC   ABCD,LABACD,1.00000000,80.2500000,One Two
2011,123456,1234567,12345678,Hey There:How are you,Hey There:How are you,882864309037,ABC   ABCD,LABACD,1.00000000,80.2500000,One Two

script :

perl script.pl infile

:

2011,123456,1234567,12345678,"Hey ThereHow are you","Hey ThereHow are you",882864309037,"ABC   ABCD",LABACD,1.00000000,80.2500000,"One Two"
2011,123456,1234567,12345678,"Hey ThereHow are younow","Hey ThereHow are younow",LABACD,1.00000000,80.2500000,"One Two"
2011,123456,1234567,12345678,"Hey There:How are you","Hey There:How are you",882864309037,"ABC   ABCD",LABACD,1.00000000,80.2500000,"One Two"

, , csv .

+1

More articles:


All Articles