Member Avatar for subok

Hi,

I need to search a particular string in a CSV file ("STPSTR01.134") the line that contains this string. I need to divide the 13th column by the 14th column.

Any help would be very much appreciated.
I'm using ActivePerl for windows

/csv file
allAssociationUtilizationData;V16SG;2012/07/27;10:45:00;STPSTR01.134;900;FALSE;SCANNER;10;3471;6288;11093;10419;588480;631296;0;254004;316732;0
allAssociationUtilizationData;V16SG;2012/07/27;10:45:00;STPSTR01.58;900;FALSE;SCANNER;10;20652;23514;39497;29404;4690132;3929776;0;3471992;2947904;0
allAssociationUtilizationData;V16SG;2012/07/27;10:45:00;STPSTR01.60;900;FALSE;SCANNER;10;20593;19351;36274;26039;4350820;3462084;0;3234956;2593056;0
allAssociationUtilizationData;V16SG;2012/07/27;10:45:00;STPSTR01.62;900;FALSE;SCANNER;10;24237;21229;40003;28678;5122408;3703840;0;3875508;2746088;0
allAssociationUtilizationData;V16SG;2012/07/27;10:45:00;STPSTR01.26;900;FALSE;SCANNER;10;18538;20644;35833;26421;4075280;3588768;0;2979124;2707892;0

  • 3 Contributors
  • 2 Replies
  • 220 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by Member #838794

Dani AI

Generated

A short, practical addendum to the thread started by : ’s split/grep one-liner is a quick way to get the job done for well-formed, simple semicolon records. For production or anything that might contain quoted fields, embedded separators, or stray whitespace, ’s Text::CSV suggestion is the safer choice. The pattern below shows a robust, easy-to-adapt approach that parses semicolon-separated rows, matches the station field exactly, trims and validates the numeric columns, protects against division-by-zero, and prints a formatted ratio.

use strict;
use warnings;
use Text::CSV;
use autodie;

open my $fh, '<:encoding(UTF-8)', 'data.csv';

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

while ( my $row = $csv->getline($fh) ) {
    next unless defined $row->[4] && $row->[4] eq 'STPSTR01.134';  # station field
    my ($num, $den) = @{$row}[12, 13];                              # 13th and 14th columns
    for ($num, $den) { s/^\s+|\s+$//g; s/,//g if defined $_ }       # trim + drop thousands commas
    next unless length $num;
    if (!defined $den || $den == 0) {
        warn "missing or zero denominator for STPSTR01.134\n";
        next;
    }
    printf "%.6f\n", $num / $den;
    last;   # remove if all matches should be processed
}

close $fh;

Notes and troubleshooting:

  • On ActivePerl, install Text::CSV (or Text::CSV_XS) via PPM/CPAN; XS is much faster.
  • Decide up-front how to handle multiple matches (per-row ratio vs aggregated numerator/denominator).
  • For extremely large integers, consider Math::BigFloat/Math::BigInt to avoid precision loss.
  • Enable auto_diag or check $csv->error_diag to reveal parsing problems.
    This complements the concise split approach while avoiding the common pitfalls of ad-hoc splitting.

Recommended Answers

All 2 Replies

Member Avatar for 2teez

Hi,
You can easily get that done like so:

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

push my @value, map { ( split /;/, $_ )[ 12, 13 ] }
  grep { /STPSTR01.134/ } <DATA>;

$value[1] <= 0
  ? print " can't divide by 0"
  : printf "%.6f", $value[0] / $value[1];

__DATA__
allAssociationUtilizationData;V16SG;2012/07/27;10:45:00;STPSTR01.134;900;FALSE;SCANNER;10;3471;6288;11093;10419;588480;631296;0;254004;316732;0
allAssociationUtilizationData;V16SG;2012/07/27;10:45:00;STPSTR01.58;900;FALSE;SCANNER;10;20652;23514;39497;29404;4690132;3929776;0;3471992;2947904;0
allAssociationUtilizationData;V16SG;2012/07/27;10:45:00;STPSTR01.60;900;FALSE;SCANNER;10;20593;19351;36274;26039;4350820;3462084;0;3234956;2593056;0
allAssociationUtilizationData;V16SG;2012/07/27;10:45:00;STPSTR01.62;900;FALSE;SCANNER;10;24237;21229;40003;28678;5122408;3703840;0;3875508;2746088;0
allAssociationUtilizationData;V16SG;2012/07/27;10:45:00;STPSTR01.26;900;FALSE;SCANNER;10;18538;20644;35833;26421;4075280;3588768;0;2979124;2707892;0
OR
use warnings;
use strict;

my @value;

while ( defined( my $line = <DATA> ) ) {
    chomp $line;
    next if $line !~ m{STPSTR01.134};
    push @value, ( split /;/, $line )[ 12, 13 ];
}

$value[1] <= 0
  ? print " can't divide by 0"
  : printf "%.6f", $value[0] / $value[1];

__DATA__
allAssociationUtilizationData;V16SG;2012/07/27;10:45:00;STPSTR01.134;900;FALSE;SCANNER;10;3471;6288;11093;10419;588480;631296;0;254004;316732;0
allAssociationUtilizationData;V16SG;2012/07/27;10:45:00;STPSTR01.58;900;FALSE;SCANNER;10;20652;23514;39497;29404;4690132;3929776;0;3471992;2947904;0
allAssociationUtilizationData;V16SG;2012/07/27;10:45:00;STPSTR01.60;900;FALSE;SCANNER;10;20593;19351;36274;26039;4350820;3462084;0;3234956;2593056;0
allAssociationUtilizationData;V16SG;2012/07/27;10:45:00;STPSTR01.62;900;FALSE;SCANNER;10;24237;21229;40003;28678;5122408;3703840;0;3875508;2746088;0
allAssociationUtilizationData;V16SG;2012/07/27;10:45:00;STPSTR01.26;900;FALSE;SCANNER;10;18538;20644;35833;26421;4075280;3588768;0;2979124;2707892;0

OUTPUT

0.017705   # modify to 6 significant figure

Please don't forget that in Perl the index of array starts from 0 not 1, so the 13th and the 14th values will be 12th and 13th, not 13th and 14th.
Hope this helps

Edited by 2teez
Member Avatar for Member #838794

use Text::CSV which makes processing CSV files easy.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.