in reply to Comparing csv text file to mysql db in order to look for missing entries within the db.

Hey there... I'm sorry you're still having trouble. I guess maybe I didn't understand exactly what it is you're trying to do?

Maybe this will help:

Add this at the top, under 'use DBI;': 

use Data::Dumper;

[download]

Then before the 'DBI->connect' line add this: 

print Dumper(\%data), "\n";

[download]

This will dump out all the data read in from your CSV file. You can look at this to make sure it "did something". If your CSV file is huge, copy the first 10 lines or so into another file, and use the small one for testing. Switch to the real one once all the debugging is done.

Now, I'm not clear whether you want to know if every IP in the CSV file exists in the DB, or if every IP in the DB exists in the CSV file. In my original reply, I assumed the IP in the DB was a primary key, and that you were wanting to check for the existence of other data fields indexed by that IP value. If you're trying to find IPs in the CSV that are missing from the database, that's a different problem! Instead of looping over the DB and comparing to the CSV, you should loop over the CSV and compare against the DB.

You might try changing your while loop as follows: 

while ( my ($nbname, $ip) = $sth->fetchrow ) {
if ($ip) {
  print "DB has IP=$ip for nbname=$nbname.\n";
} else {
  print "DB has no IP value for nbname=$nbname.\n";
    if ($data{$nbname}{'IP'}) {
      print "... but CSV file has value: $data{$nbname}{'IP'}.\n";
    }
  }
}

[download]

The key here is that this way the loop will print something either way, every time through. That "unless($ip)" in your original code means that if there IS an $ip value, nothing will print. So maybe that's what's happening (which means no errors, right? Yay! You rock!).

  • Comment on Re: Comparing csv text file to mysql db in order to look for missing entries within the db.
  • Select or Download Code

Replies are listed 'Best First'.
Re^2: Comparing csv text file to mysql db in order to look for missing entries within the db.
by carlo1973 (Initiate) on May 29, 2009 at 13:55 UTC
    Thanks again everyone and esp scorpio17 for their patience! :) Well I was asked to consult the senior IT guy to get this project done. Unfortunatly he just re-wrote my code. He tried to explain but I wasn't really able to follow as I couldn't see his screen LOL (Unfortunatly my contortionist skills aren't quite to par so bending around his "fortress of death" was a bit difficult. I honestly don't know how he gets in and out of his office with all the towers of computers around him and on his desk LOL)

    Here is the new code: 

    #!/usr/bin/perl -w
use strict;
use DBI;
use Net::Ping;
use CGI qw(:all)

# This script (should) compare a copy of the master host file to the s
+izehist database
# in order to determine what systems are currently not reporting their
+ pstsize.

my $dbh = DBI->connect( 'DBI:mysql:pstsize' );
my $sth = $dbh->prepare( 'select * from sizehist where ipaddr = ?' );

my $csv_inputfile = "/temp/use_me.txt";

my $NFcount = 0;
my $NPcount = 0;
my $ping = new Net::Ping;

my $time_input_file = "/temp/lasttime.txt";

open my $fh, $time_input_file or die "can't open $time_input_file: $! 
+\n";
while (my $line = <> ) {
chomp ( $line );
my ( $time ) = $line;
close $fh


print header("text/plain"),
start_html("Computers not reporting pstsize");
print <<EndHTML;
<h2><b><u><center>Computers not reporting Outlook PST size</h2></b></u
+></center>
<center>This will also show which of these are not responding to pings
+ (useful for troubleshooting)<br><br></center>

open my $fh, $csv_inputfile or die "can't open $csv_inputfile: $! \n";
while ( my $line = <$fh> ) {
  chomp( $line );
  my ( $name, $ip ) = split /,/, $line;
  next if $ip =~ /[^\d\.]/;
  next if $name =~ /^dhcp-/;
  next if $ip =~ /^192\.168\.8\./;

  $sth->execute( $ip );
  my $row = $sth->fetchrow_hashref;
  $sth->finish;

  unless ( $row ) {
    my $canPing = $ping->ping( $ip, 2 );
    ++$NPcount unless $canPing;
    printf "%s (%s)%s\n", $name, $ip, $canPing ? '' : ' NO PING';
    ++$NFcount;
  }
}
print "Hosts not found in DB: $NFcount\n";
print "Hosts not responding to PING: $NPcount\n";
print "The host file was last updated on $time\n";
<br>
close $fh;
EndHTML
print end_html;

    [download]
    Edit

    Well my internship is now complete. The code works! YAY!

    Now I can read those books I baught from begining to end rather than hunt and pecking through the indexes hoping that I will find something useful. I have to admit that even though that this has been trying, it has renewed my interest in studying Perl, MySQL,and PHP :) Thanks again and take care!

[reply]
[d/l]

In Section Seekers of Perl Wisdom