Musaab1
asked on
User Prompt in Perl
Hello everyone,
The code below does the funtion of trasnfer data from excel to MySQL server. the only issue is that whenever u this perl script it throws the dat onto the server. I would like to have a prompt appear on the screen asking user weather they like to send the data or not. If they say yes then it should proceed otherwise halt.
Thanks
use lib qw(../);
use Spreadsheet::ParseExcel;
my $oExcel = new Spreadsheet::ParseExcel;
sub PrnBook($);
#------------------
use Mysql;
use Data::Table;
use DBI;
#------------------
#1.1 Normal Excel97
# -------------------------- -------
# -------------------------- ------- ENTER FILENAME BELOW -------------------------- ---------- ---------- ---
# -------------------------- -------
my $oBook = $oExcel->Parse('uu.xls');
# -------------------------- -------
# -------------------------- -------
# -------------------------- -------
PrnBook($oBook);
sub PrnBook($)
{
$dbh = Mysql->connect("svv-db", "rap", "aahooja", "password");
my($oBook) = @_;
my($iR, $iC, $oWkS, $oWkC);
my %LongerNames; #Create variable
$LongerNames{'lai'}=2; #Use 2 letters from first name if last name is Lai
$LongerNames{'wang'}=3; #Use 3 letters from first name if last name is wang
$LongerNames{'jiang'}=2; #Use 2 letters from first name if last name is jiang
print "========================= ========== ======\n";
print "FILE :", $oBook->{File} , "\n";
print "COUNT :", $oBook->{SheetCount} , "\n";
print "AUTHOR:", $oBook->{Author} , "\n";
# -------------------------- -------
# -------------------------- ------- ENTER SHEET NUMBER BELOW (starting from 0) -------------------------- ---------- ---------- ---
# -------------------------- -------
$oWkS = $oBook->{Worksheet}[0];
# -------------------------- -------
# -------------------------- -------
# -------------------------- -------
print "--------- SHEET:", $oWkS->{Name}, "\n";
for(my $iR = $oWkS->{MinRow} ;
defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++) {
if ($oWkS-> {Cells}[$iR][0])
{
if ($oWkS-> {Cells}[$iR][0]->Value)
{
#$oWkC = $oWkS->{Cells}[$iR][$i];
#print $oWkC."\n";
$q = "INSERT INTO `rap`.`raptable`(Day, Week, TaskID, Tab, Network, Activity, Task, Subtask, EtimeCode, ActualCategory, Work, Person, HoursWorked, Notes, Year, Month, DateModified) VALUES (";
for ($i = 0; $i<17; $i++)
{
if (!$oWkS->{Cells}[$iR][$i])
{ $q = $q."''"; }
elsif ($i==11)
{
$name = lc($oWkS->{Cells}[$iR][$i] ->Value);
$ind = rindex ($name," ");
$last = substr($name,$ind+1);
$len =($LongerNames{$last} or 1); #use defined length, or 1 if not defined
$first = substr($name,0,$len);
print $first.$last."\n";
$q = $q."'".$first.$last."'";
}
elsif ($i==13)
{
$notes = $oWkS->{Cells}[$iR][$i]->V alue;
$notes =~ s/'/\\'/g;
$q = $q."'".$notes."'"
}
else
{ $q = $q."'".$oWkS->{Cells}[$iR] [$i]->Valu e."'"; }
if ($i!=16)
{ $q = $q.", "; }
else
{ $q = $q.")"; }
}
print $q."\n";
$dbh->query($q);
}
}
}
}
The code below does the funtion of trasnfer data from excel to MySQL server. the only issue is that whenever u this perl script it throws the dat onto the server. I would like to have a prompt appear on the screen asking user weather they like to send the data or not. If they say yes then it should proceed otherwise halt.
Thanks
use lib qw(../);
use Spreadsheet::ParseExcel;
my $oExcel = new Spreadsheet::ParseExcel;
sub PrnBook($);
#------------------
use Mysql;
use Data::Table;
use DBI;
#------------------
#1.1 Normal Excel97
# --------------------------
# --------------------------
# --------------------------
my $oBook = $oExcel->Parse('uu.xls');
# --------------------------
# --------------------------
# --------------------------
PrnBook($oBook);
sub PrnBook($)
{
$dbh = Mysql->connect("svv-db", "rap", "aahooja", "password");
my($oBook) = @_;
my($iR, $iC, $oWkS, $oWkC);
my %LongerNames; #Create variable
$LongerNames{'lai'}=2; #Use 2 letters from first name if last name is Lai
$LongerNames{'wang'}=3; #Use 3 letters from first name if last name is wang
$LongerNames{'jiang'}=2; #Use 2 letters from first name if last name is jiang
print "=========================
print "FILE :", $oBook->{File} , "\n";
print "COUNT :", $oBook->{SheetCount} , "\n";
print "AUTHOR:", $oBook->{Author} , "\n";
# --------------------------
# --------------------------
# --------------------------
$oWkS = $oBook->{Worksheet}[0];
# --------------------------
# --------------------------
# --------------------------
print "--------- SHEET:", $oWkS->{Name}, "\n";
for(my $iR = $oWkS->{MinRow} ;
defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++) {
if ($oWkS-> {Cells}[$iR][0])
{
if ($oWkS-> {Cells}[$iR][0]->Value)
{
#$oWkC = $oWkS->{Cells}[$iR][$i];
#print $oWkC."\n";
$q = "INSERT INTO `rap`.`raptable`(Day, Week, TaskID, Tab, Network, Activity, Task, Subtask, EtimeCode, ActualCategory, Work, Person, HoursWorked, Notes, Year, Month, DateModified) VALUES (";
for ($i = 0; $i<17; $i++)
{
if (!$oWkS->{Cells}[$iR][$i])
{ $q = $q."''"; }
elsif ($i==11)
{
$name = lc($oWkS->{Cells}[$iR][$i]
$ind = rindex ($name," ");
$last = substr($name,$ind+1);
$len =($LongerNames{$last} or 1); #use defined length, or 1 if not defined
$first = substr($name,0,$len);
print $first.$last."\n";
$q = $q."'".$first.$last."'";
}
elsif ($i==13)
{
$notes = $oWkS->{Cells}[$iR][$i]->V
$notes =~ s/'/\\'/g;
$q = $q."'".$notes."'"
}
else
{ $q = $q."'".$oWkS->{Cells}[$iR]
if ($i!=16)
{ $q = $q.", "; }
else
{ $q = $q.")"; }
}
print $q."\n";
$dbh->query($q);
}
}
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.