excel to database

[jeffolsen]

i am a noob to perl and web development and what i need to accomplish is to take an excel file and convert it into a database file that can just be uploaded. the problem we have now is manual insertion for the excel file into the database. i already have a site set up where someone can upload an excel file, the problem here is that i need to somehow read this file and convert it to a database file, or sql script loader. if anyone has any suggestions/sample code to get me going please let me know. thanks.

[Nedals]

There is, I beleive, a module to extract data from an Excel file. I simply convert to .CSV format and use that. It's then a relatively simple task to split the data, or use Text::CSV module, and INSERT/UPDATE into a database.

my $filename = $q->param('datafile'); # using CGI module
return 'Only a .csv file may be uploaded' unless ($filename =~ /\.csv$/i);

my @data = <$filename>; # slurp the data
my $headings = shift @data; # dump the headings

# Pre-prepare your UPDATE and INSERT statements for faster upload
my $sthUPDATE = $dbh->prepare("UPDATE table SET col1=?,col2=?,col3=?,etc WHERE id=?");
my $sthINSERT = $dbh->prepare("INSERT INTO table (id,col1,col2,col3,etc) VALUES (?,?,?,?,etc)");

my $csv = Text::CSV->new();
for (@data) {
s/(\r|\n)$//g; # Remove EOL characters (especially those inserted by $ms)
next unless ($_); # nothing on this line
$csv->parse($_);
my @coldata = $csv->fields();
my $ID = shift @coldata; # Extract the ID (in col1 and assuming there is one). Simplifies the next step
if ($sthUPDATE->execute(@coldata, $ID) eq '0E0') {
$sthINSERT->execute($ID, @coldata);
}
}

[Sixtease]

I believe the module Nedals mentions is DBD::Excel (http://search.cpan.org/~kwitknr/DBD-Excel-0.06/Excel.pm).

[bluestartech]

There is also the Parse::Excel module for dealing with excel files. But all that is trivial, a csv file would do, to put data in db (mysql in this case):

use DBI;

$dbh = DBI->connect('DBI:mysql:database_name','user','pass');

$csvfile = <$file>;

while (@csvdata) {

@data = split(/,/);

$sth = $dbh->prepare('insert into table values($data[0],$data[1]....);');
$sth->execute();
}

$dbh->close();