Perl count the hash for print

In the below code I'm trying to accumulate the product_id field and the count or occurance of the product_id for later print in the email section. See the comments in the code below, I found several examples but I'm asking for specific help within my code below.

# I need to count the occurances of product_id and assign this to a variable here... product_id may contain various product labels.

#print 'product_id = count' here with each product_id on a newline

#!/usr/bin/perl -w
use Text::CSV;
use DBI;
use strict;
use warnings;
use POSIX qw/strftime/;
use File::Basename;
$SIG{__DIE__} = \&error_handler;
my $fileimporttime = strftime('%Y-%m-%d %H:%M:%S', localtime);

# my $file = $ARGV[0];
my @files = glob("/home/it/fileprocessing/*");
my $filename = "";

foreach my $file (@files)  {
  $filename = basename($file);
  my $importsuccess = 1;
  my $recordcount = 0;
  my $client_id = (split /_/, $filename)[0];
  my $failedrecords = "";
  my $driver       = "mysql";
  my $databasename = "xxxx";
  my $dbhost       = "x.x.x.x";
  my $dbport       = "xxxx";
  my $userid       = "xxxx";
  my $password     = "xxxxxxxxx";

  my $dsn = "dbi:mysql:dbname=$databasename;host=$dbhost;port=$dbport;";
  my $dbh = DBI->connect($dsn, $userid, $password,{ RaiseError => 1})
    or die "Could not connect to database! $DBI::errstr\n";
  open (my $fh, '<', $file)
    or die "Couldn't open csvfile: $!\n";

  # prepare statement handle for reuse in the loop
  my $sth = $dbh->prepare(qq{
    INSERT INTO foobar_external_pbx_order_fulfillment(
      import_date_time,
      filename,
      client_id,
      product_id,
      order_id,
      shipment_number,
      customer_first_name,
      customer_last_name,
      address,
      city,
      state,
      zip,
      country,
      phone,
      order_date,
      email_address,
      rush_ship,
      reservation_code
    )
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}
    );
  my $sku = "";
  while (my $line = <$fh>) {
    chomp $line; # remove newline
    $line =~ s/"//g;
    $recordcount += 1;
    my @n = split /,/, $line;
    my $size = @n;
    if ($size != 16 or $client_id ne $n[0]) {
      $importsuccess = 0;
      $failedrecords .= ",$recordcount";
    }
# I need to count the occurances of product_id and assign this to a variable here... product_id may contain various product labels. 
  }
  close $fh;
  $failedrecords =~ s/^,//;
  if ($importsuccess == 1) {
    open ($fh, '<', $file)
      or die "Couldn't open csvfile: $!\n";
    while (my $line = <$fh>) {
      chomp $line; # remove newline
      $line =~ s/"//g;
      my @n = split /,/, $line;
      $sth->execute($fileimporttime, $filename, split /,/, $line ); # assuming the separator is a semicolon
    }
    close $fh;
  }
  my @row;
  my $sti;
  if ($importsuccess == 1){
    $sti = $dbh->prepare("SELECT order_date FROM foobar WHERE filename = ? GROUP BY order_date;");
    $sti->execute();
    while (@row = $dbh->selectrow_array($sql,undef,$filename));
  }

  $sti = $dbh->prepare("SELECT * FROM foobar_external_pbxs WHERE clientid = '" . $client_id ."';");
  $sti->execute();

  my $sql = "SELECT clientackemail FROM foobar_external_pbxs WHERE clientid = ?";
  @row = $dbh->selectrow_array($sql,undef,$client_id);
  unless (@row) { die "external_pbx not found in database\n"; }
  my ($to) = @row;

  my $body = "**********FILE IMPORT RESULTS**********\n";
  $body   .= 'Date: '."$fileimporttime\n";
  $body   .= 'File: '."$filename\n";
  $body   .= 'external_pbx: '."$client_id\n";
  $body   .= 'Record Count: '."$recordcount\n";
  $body   .= 'Order Dates: '."$orderdates\n";
  $body   .= 'Product Counts: '."$skucounts\n"; #print 'product_id = count' here with each product_id on a newline
  $body   .= 'Run Status: '.($importsuccess ? 'file imported successfully' : "FAILED IMPORT see records: $failedrecords\n******CORRECT THE FILE AND RESUBMIT******") ."\n";

  my $from = 'fileprocessing@bellsouth.com';
  my $subject = "external_pbx Import Results for $client_id";
  my $message = $body;

  open(MAIL, "|/usr/sbin/sendmail -t");

  # Email Header
  print MAIL "To: $to\n";
  print MAIL "From: $from\n";
  print MAIL "Subject: $subject\n\n";
  # Email Body
  print MAIL $message;

  close(MAIL);
'.processed';

}

sub error_handler {
  die @_ if $^S;          # Do nothing if we died in an eval{}
  delete $SIG{__DIE__};   # Clear handler in case following code also dies

  my $message = "\n********there was an error with the import********\n\nFilename: " . $filename;
  open(MAIL, "|/usr/sbin/sendmail -t");

    # Email Header
    print MAIL "To: itdept\@bellsouth.com\n";
    print MAIL "From: fileprocessing\@bellsouth.com\n";
    print MAIL "Subject: Error with process_incoming_file.pl\n\n";
    # Email Body
    print MAIL $message;

    close(MAIL);
'.processed';
}

Open in new window

DalexanAsked:
Who is Participating?
 
wilcoxonCommented:
You need to provide us with exactly what is in each line in the files for us to give you a definitive answer.  This should be pretty close (see comment on what you probably need to change).
#!/usr/bin/perl -w
use Text::CSV;
use DBI;
use strict;
use warnings;
use POSIX qw/strftime/;
use File::Basename;
$SIG{__DIE__} = \&error_handler;
my $fileimporttime = strftime('%Y-%m-%d %H:%M:%S', localtime);

# my $file = $ARGV[0];
my @files = glob("/home/it/fileprocessing/*");
my $filename = "";

foreach my $file (@files)  {
  $filename = basename($file);
  my $importsuccess = 1;
  my $recordcount = 0;
  my $client_id = (split /_/, $filename)[0];
  my $failedrecords = "";
  my $driver       = "mysql";
  my $databasename = "xxxx";
  my $dbhost       = "x.x.x.x";
  my $dbport       = "xxxx";
  my $userid       = "xxxx";
  my $password     = "xxxxxxxxx";

  my $dsn = "dbi:mysql:dbname=$databasename;host=$dbhost;port=$dbport;";
  my $dbh = DBI->connect($dsn, $userid, $password,{ RaiseError => 1})
    or die "Could not connect to database! $DBI::errstr\n";
  open (my $fh, '<', $file)
    or die "Couldn't open csvfile: $!\n";

  # prepare statement handle for reuse in the loop
  my $sth = $dbh->prepare(qq{
    INSERT INTO foobar_external_pbx_order_fulfillment(
      import_date_time,
      filename,
      client_id,
      product_id,
      order_id,
      shipment_number,
      customer_first_name,
      customer_last_name,
      address,
      city,
      state,
      zip,
      country,
      phone,
      order_date,
      email_address,
      rush_ship,
      reservation_code
    )
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}
    );
  my $sku = "";
  while (my $line = <$fh>) {
    chomp $line; # remove newline
    $line =~ s/"//g;
    $recordcount += 1;
    my @n = split /,/, $line;
# need to change $n[1] in the next line to whatever is the product_id
    $counts{$n[1]}++;

    my $size = @n;
    if ($size != 16 or $client_id ne $n[0]) {
      $importsuccess = 0;
      $failedrecords .= ",$recordcount";
    }
  }
  close $fh;
  $failedrecords =~ s/^,//;
  if ($importsuccess == 1) {
    open ($fh, '<', $file)
      or die "Couldn't open csvfile: $!\n";
    while (my $line = <$fh>) {
      chomp $line; # remove newline
      $line =~ s/"//g;
      my @n = split /,/, $line;
      $sth->execute($fileimporttime, $filename, split /,/, $line ); # assuming the separator is a semicolon
    }
    close $fh;
  }
  my @row;
  my $sti;
  if ($importsuccess == 1){
    $sti = $dbh->prepare("SELECT order_date FROM foobar WHERE filename = ? GROUP BY order_date;");
    $sti->execute();
    while (@row = $dbh->selectrow_array($sql,undef,$filename));
  }

  $sti = $dbh->prepare("SELECT * FROM foobar_external_pbxs WHERE clientid = '" . $client_id ."';");
  $sti->execute();

  my $sql = "SELECT clientackemail FROM foobar_external_pbxs WHERE clientid = ?";
  @row = $dbh->selectrow_array($sql,undef,$client_id);
  unless (@row) { die "external_pbx not found in database\n"; }
  my ($to) = @row;

  my $body = "**********FILE IMPORT RESULTS**********\n";
  $body   .= 'Date: '."$fileimporttime\n";
  $body   .= 'File: '."$filename\n";
  $body   .= 'external_pbx: '."$client_id\n";
  $body   .= 'Record Count: '."$recordcount\n";
  $body   .= 'Order Dates: '."$orderdates\n";
  $body   .= 'Product Counts: '.join("\t", map { "$_ = $counts{$_}" } sort keys %counts)."\n";
  $body   .= 'Run Status: '.($importsuccess ? 'file imported successfully' : "FAILED IMPORT see records: $failedrecords\n******CORRECT THE FILE AND RESUBMIT******") ."\n";

  my $from = 'fileprocessing@bellsouth.com';
  my $subject = "external_pbx Import Results for $client_id";
  my $message = $body;

  open(MAIL, "|/usr/sbin/sendmail -t");

  # Email Header
  print MAIL "To: $to\n";
  print MAIL "From: $from\n";
  print MAIL "Subject: $subject\n\n";
  # Email Body
  print MAIL $message;

  close(MAIL);
'.processed';

}

sub error_handler {
  die @_ if $^S;          # Do nothing if we died in an eval{}
  delete $SIG{__DIE__};   # Clear handler in case following code also dies

  my $message = "\n********there was an error with the import********\n\nFilename: " . $filename;
  open(MAIL, "|/usr/sbin/sendmail -t");

    # Email Header
    print MAIL "To: itdept\@bellsouth.com\n";
    print MAIL "From: fileprocessing\@bellsouth.com\n";
    print MAIL "Subject: Error with process_incoming_file.pl\n\n";
    # Email Body
    print MAIL $message;

    close(MAIL);
'.processed';
}

Open in new window

0
 
DalexanAuthor Commented:
DGAS,Server1,,,,,,,,,,,,,,
DGAS,Server2,,,,,,,,,,,,,,
DGAS,Server1,,,,,,,,,,,,,,
DGAS,Server7,,,,,,,,,,,,,,
DGAS,Server1,,,,,,,,,,,,,,
DGAS,Server7,,,,,,,,,,,,,,

So the print should be:
Server1 = 3
Server2 = 1
Server7 = 2

# need to change $n[1] in the next line to whatever is the product_id
    $counts{$n[1]}++;

$n[2] is product_id field

I'm getting errors from line 65, Global symbol "%counts" requires explicit package name
0
 
DalexanAuthor Commented:
Thanks, did not know about this map function, most excellent

my %counts;
$counts{$n[1]}++;
$body   .= 'Product Counts: '.join("\t", map { "$_ = $counts{$_}" } sort keys %counts) ."\n";
0
 
wilcoxonCommented:
Oops.  Yep.  Forgot to declare %counts...  This should fully work (left as $n[1] as if product_id = Server7 in DGAS,Server7,,,,, then that is $n[1] (DGAS is $n[0])).
#!/usr/bin/perl -w
use Text::CSV;
use DBI;
use strict;
use warnings;
use POSIX qw/strftime/;
use File::Basename;
$SIG{__DIE__} = \&error_handler;
my $fileimporttime = strftime('%Y-%m-%d %H:%M:%S', localtime);

# my $file = $ARGV[0];
my @files = glob("/home/it/fileprocessing/*");
my $filename = "";

foreach my $file (@files)  {
  $filename = basename($file);
  my $importsuccess = 1;
  my $recordcount = 0;
  my $client_id = (split /_/, $filename)[0];
  my $failedrecords = "";
  my $driver       = "mysql";
  my $databasename = "xxxx";
  my $dbhost       = "x.x.x.x";
  my $dbport       = "xxxx";
  my $userid       = "xxxx";
  my $password     = "xxxxxxxxx";

  my $dsn = "dbi:mysql:dbname=$databasename;host=$dbhost;port=$dbport;";
  my $dbh = DBI->connect($dsn, $userid, $password,{ RaiseError => 1})
    or die "Could not connect to database! $DBI::errstr\n";
  open (my $fh, '<', $file)
    or die "Couldn't open csvfile: $!\n";

  # prepare statement handle for reuse in the loop
  my $sth = $dbh->prepare(qq{
    INSERT INTO foobar_external_pbx_order_fulfillment(
      import_date_time,
      filename,
      client_id,
      product_id,
      order_id,
      shipment_number,
      customer_first_name,
      customer_last_name,
      address,
      city,
      state,
      zip,
      country,
      phone,
      order_date,
      email_address,
      rush_ship,
      reservation_code
    )
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}
    );
  my $sku = "";
  my %counts;
  while (my $line = <$fh>) {
    chomp $line; # remove newline
    $line =~ s/"//g;
    $recordcount += 1;
    my @n = split /,/, $line;
# need to change $n[1] in the next line to whatever is the product_id
    $counts{$n[1]}++;

    my $size = @n;
    if ($size != 16 or $client_id ne $n[0]) {
      $importsuccess = 0;
      $failedrecords .= ",$recordcount";
    }
  }
  close $fh;
  $failedrecords =~ s/^,//;
  if ($importsuccess == 1) {
    open ($fh, '<', $file)
      or die "Couldn't open csvfile: $!\n";
    while (my $line = <$fh>) {
      chomp $line; # remove newline
      $line =~ s/"//g;
      my @n = split /,/, $line;
      $sth->execute($fileimporttime, $filename, split /,/, $line ); # assuming the separator is a semicolon
    }
    close $fh;
  }
  my @row;
  my $sti;
  if ($importsuccess == 1){
    $sti = $dbh->prepare("SELECT order_date FROM foobar WHERE filename = ? GROUP BY order_date;");
    $sti->execute();
    while (@row = $dbh->selectrow_array($sql,undef,$filename));
  }

  $sti = $dbh->prepare("SELECT * FROM foobar_external_pbxs WHERE clientid = '" . $client_id ."';");
  $sti->execute();

  my $sql = "SELECT clientackemail FROM foobar_external_pbxs WHERE clientid = ?";
  @row = $dbh->selectrow_array($sql,undef,$client_id);
  unless (@row) { die "external_pbx not found in database\n"; }
  my ($to) = @row;

  my $body = "**********FILE IMPORT RESULTS**********\n";
  $body   .= 'Date: '."$fileimporttime\n";
  $body   .= 'File: '."$filename\n";
  $body   .= 'external_pbx: '."$client_id\n";
  $body   .= 'Record Count: '."$recordcount\n";
  $body   .= 'Order Dates: '."$orderdates\n";
  $body   .= 'Product Counts: '.join("\t", map { "$_ = $counts{$_}" } sort keys %counts)."\n";
  $body   .= 'Run Status: '.($importsuccess ? 'file imported successfully' : "FAILED IMPORT see records: $failedrecords\n******CORRECT THE FILE AND RESUBMIT******") ."\n";

  my $from = 'fileprocessing@bellsouth.com';
  my $subject = "external_pbx Import Results for $client_id";
  my $message = $body;

  open(MAIL, "|/usr/sbin/sendmail -t");

  # Email Header
  print MAIL "To: $to\n";
  print MAIL "From: $from\n";
  print MAIL "Subject: $subject\n\n";
  # Email Body
  print MAIL $message;

  close(MAIL);
'.processed';

}

sub error_handler {
  die @_ if $^S;          # Do nothing if we died in an eval{}
  delete $SIG{__DIE__};   # Clear handler in case following code also dies

  my $message = "\n********there was an error with the import********\n\nFilename: " . $filename;
  open(MAIL, "|/usr/sbin/sendmail -t");

    # Email Header
    print MAIL "To: itdept\@bellsouth.com\n";
    print MAIL "From: fileprocessing\@bellsouth.com\n";
    print MAIL "Subject: Error with process_incoming_file.pl\n\n";
    # Email Body
    print MAIL $message;

    close(MAIL);
'.processed';
}

Open in new window

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.