Member Avatar for samsons17

Hi, let say that i have this excel file that contains column of account number and the name of the customer. And I want to extract any of the data that have duplicate. And the script should be able to get the duplicate only if the both of the account number and the name of the person have been duplicated. If let say only acocunt number is duplicated, then it is not considered duplicate. Please refer the screenshot below :

duplicate

So, how suppose i do this? thanks.

  • 2 Contributors
  • 2 Replies
  • 200 Views
  • 4 Hours Discussion Span
  • Latest Post Latest Post by replic

Dani AI

Generated

As clarified, a row should be treated as a duplicate only when all three fields (account number, customer name, amount) match exactly. was right that showing an attempt helps — the snippet below is a minimal, ready-to-run Perl example you can adapt.

Save the sheet as CSV (prefer UTF-8) and let Text::CSV parse it so quoted fields and commas are handled correctly. The script trims whitespace, normalizes the amount (removes common thousand separators and currency symbols), builds a key from the three normalized fields, groups identical keys, and then prints every row that belongs to a group with more than one occurrence.

#!/usr/bin/perl
use strict;
use warnings;
use Text::CSV;

sub trim { my $s = shift // ''; $s =~ s/^\s+|\s+$//g; return $s }

my ($file, $skip_header) = @ARGV;
die "Usage: $0 file.csv [skip_header]\n" unless $file;

my $csv = Text::CSV->new({ binary => 1, auto_diag => 1 });
open my $fh, "<:encoding(utf8)", $file or die "Cannot open '$file': $!\n";

my $header = $csv->getline($fh) unless $skip_header;
my %groups;
while (my $row = $csv->getline($fh)) {
    my ($acct, $name, $amt) = @$row;            # assumes columns are account,name,amount
    $acct = trim($acct); $name = trim($name); $amt = trim($amt);
    $amt =~ s/[,\$£€]//g;                       # simple amount normalization
    $amt =~ s/\s+//g;
    my $key = join("\x1F", $acct, $name, $amt); # unlikely separator
    push @{ $groups{$key} }, $row;
}
close $fh;

my $out = Text::CSV->new({ binary => 1 });
$out->print(\*STDOUT, $header) if $header;
print "\n" if $header;
for my $k (sort keys %groups) {
    next unless @{ $groups{$k} } > 1;
    for my $r (@{ $groups{$k} }) {
        $out->print(\*STDOUT, $r);
        print "\n";
    }
}

Notes: adjust the amount-normalization regex for other currency/locale formats and strip non-breaking spaces (s/\x{A0}//g) if needed. If columns are not the first three, change the field indexes. For very large files prefer a disk-backed DB (DB_File/DBM::Deep) or an external sort+uniq pipeline to avoid high memory use. If reading .xls/.xlsx directly is required, use a reader like Spreadsheet::Read or Spreadsheet::ParseXLSX instead of exporting to CSV. This gives a concrete starting point consistent with 's advice about showing an attempt.

Recommended Answers

All 2 Replies

Member Avatar for samsons17

I am sorry. Its actually have three column, which is account number, customer name and also the amount pay by cutomer. So the program will only assume the line is duplicate if all of these three column are the same.

Please refer to the sample below :

duplicate3

Thanks :)

Member Avatar for replic

It looks like you just need someone to write a script for you. Otherwise you would have presented your attempt to solve this problem. IIRC daniweb is not here to do your homework or normal work for you.
In other words: Present some code or go pay someone to do this for you.

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.