replace/substituion 4th field

[hyans.milis]

Hi,

i'm newbie in Perl and facing the problem when creating script which replacing the 4th field from csv file. after running the script, the result is not same with my expectation.

need your help regarding substitution of 4th field. actually i will use the script in production with ~20Mil records on each file.

please advice also whether any faster method to process huge data in one file without importing to the DB.

thanks

Input :

File1.txt

623192729079,510993192729079,19,322,0,133,282051608,
623192728769,510993192728769,19,310,0,118,84950715,
623192729901,510993192729901,19,70,0,
623192609007,510993192609007,19,22,0,
623416771429,510993416771429,19,70,0,
622319309157,510992319309157,19,22,0,
623192724581,510993192724581,19,70,0,
622319381619,510992319381619,19,70,0,
622198575655,510992198575655,19,1,0,
623192724589,510993192724589,19,70,0,
622743581281,510992743581281,19,71,0,

Code :

#!/usr/bin/perl
use strict;
use warnings;
 
my $file = $ARGV[0] or die "Need to get CSV file on the command line\n
+";
 
my $sum = 0;
open(my $data, '<', $file) or die "Could not open '$file' $!\n";
 
while (my $line = <$data>) {
  chomp $line;
 
  my @fields = split "," , $line;
  my $sum = $fields[3];
     

   if ($sum > 310) {
    my $sum2= "volemd";
    chomp($sum2);
    $line =~ s/(.*)\,(.*)\,(.*)\,(.*)\,(.*)\,(.*)/$1\,$sum2\,$3\,$4,$5
+,$6/g;
    }  elsif ($sum == 70){
     my $sum3= "volemd1";
    chomp($sum3);
    $line =~ s/(.*)\,(.*)\,(.*)\,(.*)\,(.*)\,(.*)/$1\,$sum3\,$3\,$4,$5
+,$6/g; 
}
;

 print "$line\n";
}
</p>

Output :

623192729079,510993192729079,19,volemd,0,133,282051608,
623192728769,510993192728769,19,310,0,118,84950715,
623192729901,volemd1,19,70,0,
623192609007,510993192609007,19,22,0,
623416771429,volemd1,19,70,0,
622319309157,510992319309157,19,22,0,
623192724581,volemd1,19,70,0,
622319381619,volemd1,19,70,0,
622198575655,510992198575655,19,1,0,
623192724589,volemd1,19,70,0,
622743581281,510992743581281,19,71,0,
</p>

Expected result :

 
623192729079,510993192729079,19,volemd,0,133,282051608,
623192728769,510993192728769,19,310,0,118,84950715,
623192729901,510993192729901,19,volemd1,0,
623192609007,510993192609007,19,22,0,
623416771429,510993416771429,19,volemd1,0,
622319309157,510992319309157,19,22,0,
623192724581,510993192724581,19,volemd1,0,
622319381619,510992319381619,19,volemd1,0,
622198575655,510992198575655,19,1,0,
623192724589,510993192724589,19,volemd1,0,
622743581281,510992743581281,19,71,0,

[tmharish]

Considering you have already split up $line, why replace in the original? I would simply join @fields like so:

while (my $line = <$data>) {

  chomp $line;

  my @fields = split ",", $line, -1;
  my $sum = $fields[3];
     

  $fields[3] = 'volemd'  if ($fields[3] >  310 ) ;
  $fields[3] = 'volemd1' if ($fields[3] == 70  ) ;

 print join( ',', @fields ) . "\n" ;

}

Update: Modified as per correction by jwkrahn below.

[jwkrahn]

  chomp $line;
 
  my @fields = split "," , $line;

  ...

 print join( ',', @fields ) . "\n" ;

That won't work correctly on the data provided.    You need to change split "," , $line to split "," , $line, -1.

[tmharish]

Absolutely - Updated above.

[põhjapõder]

Hello hyans.milis,

You should consider using Text::CSV for this task:

#!/usr/bin/perl
use strict;
use warnings;

use Text::CSV;

die  "Usage: $0 <filename>\n" unless @ARGV > 0;
open (my $fh, '<', $ARGV[0]) || die "$ARGV[0]: $!\n";

my $csv = Text::CSV->new();

$csv->eol("\n");

my $sum = 0;
while (my $row = $csv->getline( $fh )) {
    $sum = $row->[3];

    if ($sum > 310) {
      $row->[3] = 'volemd';
    }
    elsif ($sum == 70) {
      $row->[3] = 'volemd1';
    }

    $csv->print(\*STDOUT, $row);
}
$csv->eof or $csv->error_diag();

close ($fh);

This should also work with large files, since it processes one line at a time only.

[2teez]

Hi hyans.milis,

When parsing a CSV file use tested modules like Text::CSV_XS or Text::CSV, it's a lot better than re-inventing the wheel.
However, using your method, you can achieve your aim by using non-greedy regex in your if/elsif statement blocks and putting your variables $sum2 and $sum3, in the proper place like so:

   ...
    if ( $sum > 310 ) {
        my $sum2 = "volemd";
        #chomp($sum2);  not needed
        $line =~ s/(.*?)\,(.*?)\,(.*?)\,(.*?)\,(.*?)\,(.*?)/$1\,$2\,$3
+\,$sum2,$5,$6/g;
    }
    elsif ( $sum == 70 ) {
        my $sum3 = "volemd1";
        #chomp($sum3);  not needed
        $line =~ s/(.*?)\,(.*?)\,(.*?)\,(.*?)\,(.*?)\,(.*?)/$1\,$2\,$3
+\,$sum3,$5,$6/g;
    }
 ...

That should give you your expected result:

623192729079,510993192729079,19,volemd,0,133,282051608,
623192728769,510993192728769,19,310,0,118,84950715,
623192729901,510993192729901,19,volemd1,0,
623192609007,510993192609007,19,22,0,
623416771429,510993416771429,19,volemd1,0,
622319309157,510992319309157,19,22,0,
623192724581,510993192724581,19,volemd1,0,
622319381619,510992319381619,19,volemd1,0,
622198575655,510992198575655,19,1,0,
623192724589,510993192724589,19,volemd1,0,
622743581281,510992743581281,19,71,0,

Update:
Always close your open filehandles like so: close $fh or die "can't close file: $!";

[scsijon]

I'm new to this but I'd be doing something like

open file1 for input
open file2 for output using >> so it appends
.marker1.
read a single line as variables $1, $2, ...
use switch/case to select the field(s) you want to fix or alter
change it(them) for that line of input (with case selecting fields you
+ want to alter in order)
print to (output) file2 the variables $1,$2...
check if endof input file
if not goto the next line of the input file
loop to .marker1.
if eof input file
add a print "\n"; to the output file (you usually need it)
and close both input file and output file

simple I think, but i'm a newbie

[BillKSmith]

Use perl runtime options. Specify input file on command line. Redirect output to a file with your shell.

#!perl -p
use strict;
use warnings;
my  @fields = split /,/;
$fields[3] = $fields[3] > 310 ? 'volmed' :
             $fields[3] == 70 ? 'volmedl':
             $fields[3]
             ;
$_ = join ',', @fields;

[7stud]

$line =~ s/(.*)\,(.*)\,(.*)\,(.*)\,(.*)\,(.*)/

Why are you escaping all those commas? A comma has no special regex meaning. In a regex, a comma means one thing: a literal comma.

The pattern .* says to match ANY character, 0 or more times, GREEDILY. That means that the regex engine will match as many characters as possible. When the regex engine sees a pattern like:

(.*),

...the first thing the regex engine does is find a match for the part in parentheses. Your whole line matches that part, so the regex engine reads in your whole line as the match for (.*). Then the regex engine moves on to the comma. Because there are no characters remaining in the line to match against, the regex engine backs up one character, surrendering a character from what matched .*. Then the regex engine checks if the comma matches that character. No match. So the regex engine backs up another character, surrendering yet another character from what matched .*, and the regex engine checks again if that character matches the comma. So on and so on until the regex finds a match for the comma. That's inefficient.

A more efficient regex would be this:

([^,]+),([^,]+)

However, as tmharish already mentioned you already split() your data, so there is no reason to use a regex at all. Just change whatever pieces you want in @fields like this:

$fileds[3] = "hurray";

You can limit your split to 5 instead of on every comma, which will speed things up a little.

It is possible to do a conditional s///, like this:

$line =~ s/
    (
        (?:
            [^,]+
            ,
        ){3}
    )
    ([^,]+)
    (.*)
/   $2 > 310    ? "$1volemd$3"
  : $2 == 70    ? "$1volemd1$3"
  : "$1$2$3"
  ;
/exms;

(The /e flag stands for eval.) But that is much slower than split() + join().

There is no reason to use Text::CSV_XS or any other CSV module.

[Anonymous Monk]

• There is no reason to use Text::CSV_XS or any other CSV module. For a csv file? Why is that?
• (The /e flag stands for eval.) No sir, the /e modifier does NOT means eval.
  This is what perlop said about it; A /e will cause the replacement portion to be treated as a full-fledged Perl expression and evaluated right then and there. It is, however, syntax checked at compile-time.
  It is when you have /ee, that the second "e" evaled, the expression. Please read it up.

[7stud]

There is no reason to use Text::CSV_XS or any other CSV module. For a csv file? Why is that?

Because perl natively supplies a simple and efficient tool to do what the op wants.

It is evaled when you have /ee, that the second "e" evaled, the expression. Please read it up.

I originally had two ee's, but that was so slow compared to split(), I changed it to one e.

It is, however, syntax checked at compile-time.

++ perl savoir-faire.

[hyans.milis]

thanks so much for the help. it works now