Data Comparision in perl

Hi,

Can anyone suggest how to compare data between a txt file and Database.
new_perl_userAsked:
Who is Participating?
 
wilcoxonConnect With a Mentor Commented:
Give this a try...

The problem is that line 13 (and 30) remove entries from %ids.  I created a copy of %ids prior to looping.  I also modified the script to re-use $count/$sel/$subref since there's no reason to create new vars (and potentially use extra memory).
foreach my $fil (@files) {
    # read the ids from the files
    open IN, "$dir/$fil" or die "could not read $fil: $!";
    my %ids = map { $_ => 1 } map { chomp; split /\s+/ } <IN>;
    my %ori_ids = %ids;
    close IN;
    # read ids from the db

    my $count;
    my $sel=$db->prepare("select (number||'/'||id) as List_ID from info where (number||'/'||id)  in (" . join(', ', map { "'$_'" } keys %ids) . ')');
    $sel->execute();
    # remove ids from the file list if found in the db
    while(my $subref = $sel->fetchrow_hashref()) {
        delete $ids{$subref->{'LIST_ID'}};
        count++;
    }
    print $count, "\n";
    $sel->finish();
    # any ids left in %ids were not found in the db
    if (%ids) {
        print $_, "\n" foreach (keys %ids);
    } else {
        print "ids match\n";
    }

    %ids = %ori_ids;
    $count = 0;
    $sel=$db->prepare("select (number||'/'||id) as List_ID2 from info2 where (number||'/'||id)  in (" . join(', ', map { "'$_'" } keys %ids) . ')');
    $sel->execute();
    # remove ids from the file list if found in the db
    while(my $subref = $sel1->fetchrow_hashref()) {
        delete $ids{$subref->{'LIST_ID2'}};
        $count++;
    }
    print $count, "\n";
    $sel->finish();
    # any ids left in %ids were not found in the db
    if (%ids) {
        print $_, "\n" foreach (keys %ids);
    } else {
        print "ids match\n";
    }
}

Open in new window

0
 
ozoCommented:
if they are string data, you might use the cmp operator
if they are numerical data, you might use the <=> operator
0
 
new_perl_userAuthor Commented:
they are alphanumeric data like:

DHIBATCH0001/4567893W
DHIBATCH0001/46569709W
DHIBATCH0002/2345692562
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ozoCommented:
the cmp or eq operators can be used to determine whether two string operands are the same or different.
0
 
new_perl_userAuthor Commented:
I was trying something like below it is not working. If not equal should print the unmatching id's


if (@ids eq $number){

print "ids match";

}

else{

print "$number\n";

}
0
 
ozoCommented:
@ids is not a string, it is an array.
For what values of @ids and $number would you want to print "ids match"?
and for what values of @ids and $number would you want to print "$number\n"?
0
 
wilcoxonCommented:
You are trying to compare a list to a scalar (which won't work).

In "if (@ids eq $number){", will @ids contain things like your example? eg:

@ids = (DHIBATCH0001/4567893W, DHIBATCH0001/46569709W, DHIBATCH0002/2345692562)

Will $number contain a single similar id?

Assuming the above is correct and you just want to find out if $number occurs somewhere in @ids then you can do:
if (grep { $_ eq $number } @ids) {
    print "ids match";
} else {
    print $number, "\n";
}

Open in new window

0
 
new_perl_userAuthor Commented:
Here is what I am doing:

foreach my $fil (@files) {

# read the ids from the files

    open IN, "$dir/$fil" or die "could not read $fil: $!";


    my @ids = map { "'$_'" } map { chomp; split /\s+/ } <IN>;

my $sel=$db->prepare("select (number||'/'||id) as List_ID from info where (number||'/'||id)  in (" . join(', ', @ids) . ')');

   $sel->execute();
    

while(my $subref = $sel->fetchrow_hashref()) {

   my $number=$subref->{'LIST_ID'};


if (@ids eq $number){

print "ids match";

}

else{

print "$number\n";

}

Open in new window


so  when it is query's the DB using SELECT if it is able to find all id's from file then Print "id's match". If  any id is not matching  then Print " the id's that don't match
0
 
wilcoxonCommented:
The if line I provided above should work in your code - I modified it here.
foreach my $fil (@files) {

    # read the ids from the files

    open IN, "$dir/$fil" or die "could not read $fil: $!";

    my @ids = map { "'$_'" } map { chomp; split /\s+/ } <IN>;

    close IN;

    my $sel=$db->prepare("select (number||'/'||id) as List_ID from info where (number||'/'||id)  in (" . join(', ', @ids) . ')');

    $sel->execute();
    
    while(my $subref = $sel->fetchrow_hashref()) {

        my $number=$subref->{'LIST_ID'};

        if (grep { $_ eq $number } @ids) {

            print "ids match";

        }

        else {

            print "$number\n";

        }
    }
}

Open in new window

0
 
new_perl_userAuthor Commented:
Hi,

I tried the above logic, but it is printing all the ID's  although they match.

0
 
wilcoxonCommented:
Rereading your comment following your code, the above doesn't quite do what you want.  This should...
foreach my $fil (@files) {
    # read the ids from the files
    open IN, "$dir/$fil" or die "could not read $fil: $!";
    my %ids = map { $_ => 1 } map { chomp; split /\s+/ } <IN>;
    close IN;
    # read ids from the db
    my $sel=$db->prepare("select (number||'/'||id) as List_ID from info where (number||'/'||id)  in (" . join(', ', map { "'$_'" } keys %ids) . ')');
    $sel->execute();
    # remove ids from the file list if found in the db
    while(my $subref = $sel->fetchrow_hashref()) {
        delete $ids{$subref->{'LIST_ID'}};
    }
    $sel->finish();
    # any ids left in %ids were not found in the db
    if (%ids) {
        print $_, "\n" foreach (keys %ids);
    } else {
        print "ids match\n";
    }
}

Open in new window

0
 
wilcoxonCommented:
Yes, there were two problems in the original.

1) The problem that was causing all ids to print was that @ids contained the quoted identifiers but the identifiers returned from the db were not quoted.

2) The other problem was a logical issue with the way you were trying to approach it - since you were looping over the database values and the database would only return values that were in the list from the file, then it would never not match in your if (once #1 was fixed).

The second code I provided should work (it fixes both issues).
0
 
new_perl_userAuthor Commented:
Hi,

Thanks a lot it is working. Need help regarding two things. I went a head and extended the script to compare the same log file data against another table in DB.

Everything is working, but I am not bale to get the Count of Id's in Log file and Count of id's from Second table.

foreach my $fil (@files) {
    # read the ids from the files
    open IN, "$dir/$fil" or die "could not read $fil: $!";
    my %ids = map { $_ => 1 } map { chomp; split /\s+/ } <IN>;
    close IN;
    # read ids from the db

my $count;
    my $sel=$db->prepare("select (number||'/'||id) as List_ID from info where (number||'/'||id)  in (" . join(', ', map { "'$_'" } keys %ids) . ')');
    $sel->execute();
    # remove ids from the file list if found in the db
    while(my $subref = $sel->fetchrow_hashref()) {
        delete $ids{$subref->{'LIST_ID'}};
count ++;
    }
print "$count\n";
    $sel->finish();
    # any ids left in %ids were not found in the db
    if (%ids) {
        print $_, "\n" foreach (keys %ids);
    } else {
        print "ids match\n";
    }

my $count2;
my $sel1=$db->prepare("select (number||'/'||id) as List_ID2 from info2 where (number||'/'||id)  in (" . join(', ', map { "'$_'" } keys %ids) . ')');
    $sel1->execute();
    # remove ids from the file list if found in the db
    while(my $subref1 = $sel1->fetchrow_hashref()) {
        delete $ids{$subref1->{'LIST_ID2'}};
  $count2++;
    }
print "$count2\n";
    $sel1->finish();
    # any ids left in %ids were not found in the db
    if (%ids) {
        print $_, "\n" foreach (keys %ids);
    } else {
        print "ids match\n";
    }
}

Open in new window

0
 
new_perl_userAuthor Commented:
Hi,

Thank you it is working for count of id's from second table but not for
"Count of Id's in Log file" ( the file it is reading)
0
 
new_perl_userAuthor Commented:
Tried something like this but not working.

my $i = scalar keys %ori_ids;
print $i"\n"
0
 
new_perl_userAuthor Commented:
Able to get it.

my $i = keys(%ids);
print$i "\n";
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.