I have ported several table from access to MySQL with good results.
Within Access right click on the table you are interested in and export, then select odbc data sources and off you go Regards John B -----Original Message----- From: Nitin Mehta [mailto:[EMAIL PROTECTED] Sent: 09 January 2004 15:11 To: [EMAIL PROTECTED] Subject: Re: Converting an Access Table to MySQL you need not write any scripts, just use MySQL Front to Import/Export dat from M$ Access "zzapper" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
