[lug] Converting MS-Access to text/MySQL

[Rob Nagler]

Jeff Schroeder writes:
> import directly to MySQL.  This needs to be scripted (via cron, 
> probably) so I can automatically parse and import the Access data.

DBD::ODBC works great.  Replace myfile, my_table, 65535, etc. below:

    use strict;
    use DBI ();
    my($dbh) = DBI->connect(
        'dbi:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=myfile.mdb',
	{
	    RaiseError => 1,
	    AutoCommit => 0,
	    LongReadLen => 65535,
	}
    ) || die($DBI::errstr);
    # Bug which requires this be set here, too.
    $dbh->{LongReadLen} = 65535;
    my($sth) = $dbh->prepare('select * from my_table');
    $sth->execute;
    while (my $row = $sth->fetchrow_arrayref) {
        print "First Col is: ", $row->[0], "\n";
    }

If you need perl for Win32, visit http://activestate.com/Products/ActivePerl/
If it needs to run on Linux, you can set up an ODBC bridge.  

Rob