keep it simple.. save the excel sheet as a csv file, then have a look at LOAD DATA INFILE. As long as MySQL can read the file, an sql command such as this should do it:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
    ->           FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    ->           LINES TERMINATED BY '\n';
[download]

cheers,

J

Hello,

I'll give this a try. Sounds pretty straightfoward. Thanks for the input.

Hi,

I'm working on loading the data into a table. I'm receiving an error message that the text file cannot be located.

Here's the exact statement I'm typing in that calls in the text file.

mysql> LOAD DATA LOCAL INFILE 'C:\\Helen\\file.csv' INTO TABLE test

The rest of the command is the same as you gave me. I've read through the MySQL book on how to read a local file and I'm not sure what's happening.

Any ideas? Thanks.

Here's some untested code, but it's adapted from a script I wrote. It should be enough to get you going in the right direction. Hope this helps.

#!/usr/bin/perl

use strict;
use DBI;
use Spreadsheet::ParseExcel;

# connect to DB & prepare statement handle
my $dbh = DBI->connect("your:dsn:here");
my $sth = $dbh->prepare("INSERT INTO table (foo,bar) values(?,?)");

# get workbook
my $file = $ARGV[0] || usage();
my $parser = Spreadsheet::ParseExcel->new();
my $book = $parser->Parse($file) || die("No workbook found!");

# get the first worksheet
my $sheet = $book->{Worksheet}[0];
my ($min, $max) = $sheet->RowRange();

# iterate over each row...
foreach my $row ($min..$max) {
    # get the values in each column you want...
    my $name = $sheet->Cell($row,1)->Value;
    my $address = $sheet->Cell($row,2)->Value();

    # and add a new row
    $sth->execute($name, $address);
}

$dbh->disconnect();
[download]

I used Spreadsheet::ParseExcel with some success. Try that.

Don't ask me for the details, but it seems to me you should be able to load the Excel file into MS Access---assuming you have the whole Office package installed. It might involve saving the file as comma-separated values, and then using Access to connect to MySQL. At my last job we used Access as a desktop DB client, but while I configured my machine, I never understood the details.

Sorry for an unhelpful answer, but perhaps checkign the Access manual will provide the info you need.