#!perl
use strict;
use Text::CSV_XS;
use File::BOM;
use Data::Dump 'pp';

my $infile  = $ARGV[0] || 'c:/temp/data.csv';
my $outfile = 'c:/temp/ModCSV.csv';

open my $ORIG_CSV, '<:via(File::BOM)', $infile
   or die "Can't open $infile: $!";
open my $MOD_CSV, '>:raw:encoding(iso-8859-1)', $outfile
   or die "Can't open $outfile: $!";

my $aCSV = Text::CSV_XS->new({
  eol                   => undef, # \r, \n, or \r\n
  quote                 => '"',
  binary                => 1,
  allow_whitespace      => 1,
  allow_loose_quotes    => 1,
});

my %i=(); # used to summate
my @rows;
my %count=(); # used to remove duplicates
while (my $row = $aCSV->getline($ORIG_CSV)) {
  if ($row->[0] =~ /\S/ ) {
    my $key = $row->[3];
    if ($row->[1] =~ /^\d+/) {
      $i{$key} += $row->[1];
    }
    # remove last 2 columns
    splice @$row,-2;
    
    # only save 1 record for each key
    push @rows, $row unless ($count{$key}++); 
  }    
}
close $ORIG_CSV;

# show the summation 
for (sort keys %i){
  print "$i{$_} $_\n";
}

# update @rows with sum
my $n=0;
for (@rows){
  my $key = $_->[3];
  next if $key eq 'textbox85'; # skip header
  $_->[1] = $i{$key};
  $_->[0] = ++$n; # re-number
}

# output @rows
$aCSV->say($MOD_CSV, $_) for @rows;