Converting an Access Table to MySQL

[zzapper]

Hi Ya,

How to this has always been somewhat fuzzy in my head. I looked around
for tools unsuccessfully . So I thought I'd share my solution.

I've just written a simple Perl script to do this, this uses DBI:mysql
to write to mysql and DBI:ODBC to read from Access. I suppose it could
be rewritten in PHP as well.


The tables map practically one to one, but the script could easily be
adapted for a more complex conversion. I presume it could be adapted
to do a reverse conversion as well.

Any suggestions/improvements welcome

#!/usr/local/bin/perl
# convert.pl
# description : Copy Access Database via DBI:ODBC to MySQL
# V1.0 07/01/2004

my $dsn="group";
my $dsn_mysql="group";
my $table="group";
my $table_dest="ytbl_agents";
my $db; # database handle
my $db_dest; # database handle

$delete_sql=qq|DROP TABLE IF EXISTS ytbl_agents|;
$recreate_table =qq|
CREATE TABLE ytbl_agents (
  intID mediumint(9) unsigned NOT NULL auto_increment,
  txtRegion varchar(60) default NULL,
  txtCompany varchar(60) default NULL,
  txtContact varchar(60) default NULL,
  txtAddress text,
  txtTown varchar(60) default NULL,
  txtCounty varchar(60) default NULL,
  txtPostCode varchar(20) default NULL,
  txtPhone varchar(60) default NULL,
  txtFax varchar(50) default NULL,
  txtEmail varchar(60) default NULL,
  txtWeb varchar(60) default NULL,
  dtmDate timestamp(14) default NULL,
  PRIMARY KEY  (intID)
) TYPE=MyISAM;
|;

# YYYYMMDDHHMMSS
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =
localtime(time);
$year+=1900; $mon++;
$mysql_timestamp = sprintf(
"%04d%02d%02d%02d%02d%02d",$year,$mon,$mday,$hour,$min,$sec);
#-------------------------------------------------------------#

&open_access_db();
&open_mysql_db();
$cnt=&fn_read_access_db();
&fn_write_to_mysql();
print "\n$cnt records imported to mysql database $dsn_mysql (table
$table_dest)\n";
$db->disconnect();
$db_dest->disconnect();

#-------------------------------------------------------------#

sub open_access_db()
{
use DBI;
use DBD::ODBC; 
 
my $emsg="Could not access the Database\n Could not open DSN $dsn";

$db = DBI->connect( "dbi:ODBC:$dsn", "", "",
    {RaiseError => 1,
     PrintError => 1,
     AutoCommit => 1,
     LongReadLen => 4000} ) or
do
{
print ("$emsg: " . $DBI::errstr .__LINE__."\n");
exit;
}
}
#-------------------------------------------------------------#


sub open_mysql_db()
{
use DBI;
use DBD::MYSQL; 
 
my $emsg="Could not access the Database\n Could not open DSN
$dsn_mysql";

$db_dest = DBI->connect( "dbi:mysql:$dsn_mysql", "", "",
    {RaiseError => 1,
     PrintError => 1,
     AutoCommit => 1,
     LongReadLen => 4000} ) or
do
{
print ("$emsg: " . $DBI::errstr .__LINE__."\n");
exit;
};
### delete table
$sel_dest = $db_dest->prepare( "$delete_sql" );
$sel_dest->execute() or &webdie("\n$delete_sql \n: ".$DBI::errstr."
line ".__LINE__);
### recreate table
my $sel_dest = $db_dest->prepare( "$recreate_table" );
$sel_dest->execute() or &webdie("\n$recreate_table \n:
".$DBI::errstr." line ".__LINE__);

}
#-------------------------------------------------------------#
sub fn_read_access_db
{
   my $sql=qq|select * from "$table" |;
   my $emsg="$sql";
   my $sel = $db->prepare( "$sql" );
   $sel->execute() or &webdie("$emsg : ".$DBI::errstr." line
".__LINE__);

   @results=();
   while (my $ref=$sel->fetchrow_hashref)
   {
      push @results, {%$ref}; # array of hashes
      undef $ref;
   }
   return $#results+1;
}
#-------------------------------------------------------------#
sub fn_write_to_mysql()
{
my $cols =qq|txtRegion,  txtCompany, txtContact, txtAddress, txtTown,
txtCounty, txtPostCode|;
   $cols.=qq|, txtPhone, txtFax, txtEmail, txtWeb, dtmDate|;
for my $resid (0 .. $#results)
{
my $ID= $results[$resid]{"ID"};
my $region=escsql($results[$resid]{"region"});
my $company=escsql($results[$resid]{"company"});
my $contact=escsql($results[$resid]{"contact"});
my $address=escsql($results[$resid]{"address"});
my $town=escsql($results[$resid]{"town"});
my $county=escsql($results[$resid]{"county"});
my $postcode=escsql($results[$resid]{"postcode"});
my $tel=escsql($results[$resid]{"tel"});
my $fax=escsql($results[$resid]{"fax"});
my $email=escsql($results[$resid]{"email"});
my $web=escsql($results[$resid]{"web"});

my $vals
=qq|'$region','$company','$contact','$address','$town','$county','$postcode','$tel','$fax','$email','$web'|;
   $vals.=qq|,'$mysql_timestamp'|;
#print $cols; print "\n".$vals; exit;
my $sql=qq|insert into $table_dest |;
      $sql.=qq| ($cols) values ($vals)   |;
my $sel_dest = $db_dest->prepare( "$sql" );
   $sel_dest->execute() or &webdie("\n$sql \n: ".$DBI::errstr." line
".__LINE__);
}
}
#-------------------------------------------------------------#
sub escsql()
{
    my ($inputstr) = @_ ;
    return "" if (!defined($inputstr)) ; 
    $inputstr =~ s/\\/\\\\/g;
    $inputstr =~ s/\"/\\"/g;
    $inputstr =~ s/\'/\\'/g;
    return($inputstr) ;
}
#-------------------------------------------------------------#

zzapper (vim & cygwin & zsh)
--

vim -c ":%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?"

http://www.vim.org/tips/tip.php?tip_id=305  Best of Vim Tips


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]