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] >
