new_perl_user
asked on
Data Comparision in perl
Hi,
Can anyone suggest how to compare data between a txt file and Database.
Can anyone suggest how to compare data between a txt file and Database.
ASKER
they are alphanumeric data like:
DHIBATCH0001/4567893W
DHIBATCH0001/46569709W
DHIBATCH0002/2345692562
DHIBATCH0001/4567893W
DHIBATCH0001/46569709W
DHIBATCH0002/2345692562
the cmp or eq operators can be used to determine whether two string operands are the same or different.
ASKER
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";
}
if (@ids eq $number){
print "ids match";
}
else{
print "$number\n";
}
@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"?
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"?
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:
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";
}
ASKER
Here is what I am doing:
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
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";
}
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
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";
}
}
}
ASKER
Hi,
I tried the above logic, but it is printing all the ID's although they match.
I tried the above logic, but it is printing all the ID's although they match.
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";
}
}
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).
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).
ASKER
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.
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";
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)
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)
ASKER
Tried something like this but not working.
my $i = scalar keys %ori_ids;
print $i"\n"
my $i = scalar keys %ori_ids;
print $i"\n"
ASKER
Able to get it.
my $i = keys(%ids);
print$i "\n";
my $i = keys(%ids);
print$i "\n";
if they are numerical data, you might use the <=> operator