Spreadsheet::ParseExcel::Simple
Spreadsheet::ParseExcel
DBI
the perl script
#!/usr/bin/perl
use DBI;
use Spreadsheet::ParseExcel::Simple;
use Spreadsheet::ParseExcel;
my $workbook = Spreadsheet::ParseExcel::Workbook->Parse('Agent Details.xls');
#==================================================
#==================================================
foreach $sheet (@{$workbook->{Worksheet}}) {
foreach $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {
if ($sheet->{Cells}[0][$col]->{Val} eq "COL1") {
$col1= $col;
}
if ($sheet->{Cells}[0][$col]->{Val} eq "COL2") {
$col2= $col;
}
if ($sheet->{Cells}[0][$col]->{Val} eq "COL3") {
$col3= $col;
}
if ($sheet->{Cells}[0][$col]->{Val} eq "COL4") {
$col4= $col;
}
}
#==================================================
#==================================================
foreach $row ($sheet->{MinRow}+1 .. $sheet->{MaxRow}) {
$col_1 = $sheet->{Cells}[$row][$col1]->{Val};
$col_2 = $sheet->{Cells}[$row][$col2]->{Val};
$col_3 = $sheet->{Cells}[$row][$col3]->{Val};
$col_4 = $sheet->{Cells}[$row][$col4]->{Val};
#==================================================
#==================================================
if( $col_1!= ' ' && $col_2!= ' ' && col_3 !=' ' && col_4 != ' ')
{
$dbh = DBI->connect("DBI:mysql:databasename:localhost:3306", "username", "password")
or die "Couldn't connect to database: " . DBI->errstr;
$stmt = "INSERT INTO databasename.tablename (col1,col2,col3,col4) values ( '$col_1' ,'$col_2','$col_3','$col_4');";
$affected_rows = $dbh->do($stmt);
}
}
}
#==================================================

No comments:
Post a Comment