Am I on the right track? Is there perhaps a more direct conversion path from Excel to mysql?

There is. Just save the data as a tab separated txt file from within Excel. This is a standard save as option. Then do this in mysql client after you have created the table (which you can also do in mysql client):

mysql> LOAD DATA LOCAL INFILE '/path/to/datafile.txt' INTO TABLE table
+_name;
[download]

It will load the data at a rate > 10,000 records a second so it should not take too long. The only reason to do it the slow way using DBI and DBD::mysql is if you need to do some on the fly data munging in transit.

You would create the table in the mysql client like:

mysql> CREATE TABLE jokes ( id int not null, name char(32), timestamp 
+date, joker char(32) );
[download]

You can use ALTER TABLE to add keys after the import or create the keys with the table. If your fields are longer than the allocated space they will be uncerimoniously truncated to fit so allocate enough room. Note char() is faster than varchar() per se but uses more space.

Wow, thanks! I've never dreamt it could be so easy :)

Btw, what if some of the columns have empty values? Would it still work. I'll know it when I try out later...

Welcome to the non M$ world ;-) The is a substantial upside to not rotating around the GUI.

Anyway with empty fields yes it *should* still work. Excel dumps empty fields as

field1\t\tfield3\tfield4
[download]

So when mysql splits on \t you get an undef (NULL) value. In that case add "default 'blah'" to the create ie:

timestamp date default '1970-01-01',
joker char(32) default 'anon',
[download]

and mysql will fill in the blanks.

If your data is really dirty you may wish to parse the tab sep file with perl, check for whatever then write out all the clean data to a fresh tab sep file before you import. You could flag the errors, write them to a tab sep file and then open and fix it in Excel or just automate it.

One thing to be aware of is that you probably will want a primary key on id (so id must be not null and must also be unique). The other key/index options you will probably want is an INDEX on some/all of the other fields depending on how you plan to search it.

cheers

tachyon

Tab delimited?! Ew! That just makes it difficult to read records that span lines.

Actually impossible from the point of view of an import utility:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]
    ]
    [LINES 
        [STARTING BY '']    
        [TERMINATED BY '\n']
    ]
    [IGNORE number LINES]
    [(col_name,...)]
[download]

Embedded newlines make it hard to define what represents a 'row'. Fortunately AFAIK you can't insert newlines into an Excel cell string as it triggers moving you to a new cell.

cheers

tachyon

There is also a command line program called mysqlimport which is delivered with mysql. It is a front end for tachyon's excellent suggestion. It doesn't seem to have a man page, but you can find it in the mysql info pages - info mysql.

After Compline,
Zaxo

Of course, you could save the Excel file as a CSV and use whatever loader(s) MySQL provides to load the CSV file.

That said, whatever ends up working is the best thing to do.

Thanks diotalevi!

I've problems binding the values from the parsed Excel file to the DBI execute command. How exactly do I bind those values?

use strict;
use Spreadsheet::ParseExcel;
use Data::Dumper;

my $source_file = 'C:/mydocu~1/db/jokes.xls';

my $source_excel = new Spreadsheet::ParseExcel;
my $source_book = $source_excel->Parse($source_file);

my $dbh = do_connect();

my $sql = "INSERT INTO mytest VALUES(?,?,?,?);
my $sth = $dbh->prepare($sql);

# code lifted from a site...
my($iR, $iC, $oWkS, $oWkC);
foreach my $oWkS (@{$source_book->{Worksheet}}) {
   for(my $iR = $oWkS->{MinRow} ; 
      defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++) {
      for(my $iC = $oWkS->{MinCol} ;
         defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ; $iC++) {
            $oWkC = $oWkS->{Cells}[$iR][$iC];
            print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC);   
+         
      }
   }
}

# $sth->execute(var1, var2, var3, var4);
# I know the above line has to be somewhere in the foreach loop but I 
+just can't figure how :(
[download]

cheers,

Please read the thread. It provides much advice on how to write the code to import the data.

What I want is to parse the excel file using ParseExcel and then have the perl script write the necessary sql statements into a outputfile for example insert statements.