[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Data Comparision in perl

Posted on 2011-10-17
16
Medium Priority
?
235 Views
Last Modified: 2012-05-12
Hi,

Can anyone suggest how to compare data between a txt file and Database.
0
Comment
Question by:new_perl_user
  • 8
  • 5
  • 3
16 Comments
 
LVL 85

Expert Comment

by:ozo
ID: 36980896
if they are string data, you might use the cmp operator
if they are numerical data, you might use the <=> operator
0
 

Author Comment

by:new_perl_user
ID: 36980907
they are alphanumeric data like:

DHIBATCH0001/4567893W
DHIBATCH0001/46569709W
DHIBATCH0002/2345692562
0
 
LVL 85

Expert Comment

by:ozo
ID: 36980926
the cmp or eq operators can be used to determine whether two string operands are the same or different.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:new_perl_user
ID: 36980985
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
 
LVL 85

Expert Comment

by:ozo
ID: 36981076
@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
 
LVL 27

Expert Comment

by:wilcoxon
ID: 36981109
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
 

Author Comment

by:new_perl_user
ID: 36981136
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
 
LVL 27

Expert Comment

by:wilcoxon
ID: 36981365
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
 

Author Comment

by:new_perl_user
ID: 36981471
Hi,

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

0
 
LVL 27

Expert Comment

by:wilcoxon
ID: 36981489
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
 
LVL 27

Expert Comment

by:wilcoxon
ID: 36981516
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
 

Author Comment

by:new_perl_user
ID: 36981833
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
 
LVL 27

Accepted Solution

by:
wilcoxon earned 2000 total points
ID: 36982164
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
 

Author Comment

by:new_perl_user
ID: 36982390
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
 

Author Comment

by:new_perl_user
ID: 36982525
Tried something like this but not working.

my $i = scalar keys %ori_ids;
print $i"\n"
0
 

Author Comment

by:new_perl_user
ID: 36982666
Able to get it.

my $i = keys(%ids);
print$i "\n";
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've just discovered very important differences between Windows an Unix formats in Perl,at least 5.xx.. MOST IMPORTANT: Use Unix file format while saving Your script. otherwise it will have ^M s or smth likely weird in the EOL, Then DO NOT use m…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question