//
you're reading...
OpenSource, Samples, scripting

Excel to MySQL Importer With ActivePerl

1) CREATE DATABASE `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
2) CREATE USER 'mydbusr'@'%' IDENTIFIED BY 'mypassword';
3) GRANT USAGE ON * . * TO 'mydbusr'@'%' IDENTIFIED BY 'mypassword' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
4) GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , REFERENCES , INDEX , ALTER , CREATE TEMPORARY TABLES , LOCK TABLES , CREATE VIEW , SHOW VIEW , EXECUTE ON `mydb` . * TO 'mydbusr'@'%';
5) FLUSH PRIVILEGES ;
 6) CREATE TABLE `Events_tbl` (
`myDATE` datetime NOT NULL,
`Employee_name` varchar(70) NOT NULL,
`Shift_emp` varchar(70) NOT NULL,
`Worktype` varchar(70) NOT NULL,
`Leavetype` varchar(50) NOT NULL,
`Location` varchar(50) NOT NULL
) ENGINE=MyISAM;

#!/usr/bin/perl

use DBI;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

$Win32::OLE::Warn = 3;

# MySQL connection Settings

my $data_source = "dbi:mysql:mydb;host=mysql.mydomain.com";
my $user = "mydbusr";
my $password ="mypassword";
my $dbh = DBI->connect($data_source, $user, $password)
or die "Can't connect to $data_source: $DBI::errstr";

$args = $#ARGV + 1;

if ($args!=1)
{
print "Excel to MySQL Importer - coded by Panagiotis Iliopoulos\n";
print "Syntax: excelp.pl \n";

}
else
{

$num_of_line=$ARGV[0];
$num_of_line++;

# get already active Excel application or open new
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
Win32::OLE->new('Excel.Application', 'Quit');

# open Excel file
my $Book = $Excel->Workbooks->Open("C:/ms_access/excel/DynLists.xls");

# select worksheet number 1 (you can also select a worksheet by name)
my $Sheet = $Book->Worksheets(2);

$a=$Sheet->Cells(3,1)->{'Formula'};

$b=scalar localtime(($a-25569)*86400);

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(($a-25569)*86400);

$mday=$mday;
$mon=$mon+1;
$year=$year-100+2000;

foreach my $row (2..$num_of_line)
{
$myDATE="";
$Employee_name="";
$Shift_emp="";
$Worktype="";
$Leave="";
$Location="";
$count=1;
foreach my $col (1..6)
{
# skip empty cells
next unless defined $Sheet->Cells($row,$col)->{'Value'};

if ($col==1){

 $a=$Sheet->Cells($row,1)->{'Formula'};
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(($a-25569)*86400);

$mday=$mday;
$mon=$mon+1;
$year=$year-100+2000;

print "$mday-$mon-$year";
$myDATE=$year.'-'.$mon.'-'.$mday;

}
else{
$cel_value=scalar $Sheet->Cells($row,$col)->{'Value'};
if ($count==1)  {$Employee_name=$cel_value;}
if ($count==2)  {$Shift_emp=$cel_value;}
if ($count==3)  {$Worktype=$cel_value;}
if ($count==4)  {$Leave=$cel_value; $Leave=~s/ //g;}
if ($count==5)  {$Location=$cel_value;}

$count++;

print ",$cel_value";
}#else
}#foreach column
print "\n";
print "===>$myDATE,$Employee_name,$Shift_emp,$Worktype,Leave,$Location\n";

# execute INSERT query

$sql_stmt = q{
     INSERT INTO Events_tbl (myDATE,Employee_name,Shift_emp,Worktype,Leavetype,Location)
     VALUES (?,?,?,?,?,?)
};

my $sth = $dbh->prepare( $sql_stmt ) or die "Can't prepare statement: $DBI::errstr";
my $rc = $sth->execute($myDATE,$Employee_name,$Shift_emp,$Worktype,$Leave,$Location) or die "Can't execute statement: $DBI::errstr";

$sth->finish;

}#foreach row

# clean up after ourselves
$Book->Close;
}#else

$dbh->disconnect();

Discussion

Comments are closed.

%d bloggers like this: