Re: Converting an Access Table to MySQL
Victor, It's at the same URL www.mysqlfront.de I'm not sure it's stable yet Subject: Re: Converting an Access Table to MySQL From: Victor Medina <[EMAIL PROTECTED]> To: zzapper <[EMAIL PROTECTED]> Copies to: [EMAIL PROTECTED] Date sent: Fri, 09 Jan 2004 11:48:42 -0400 On Fri, 2004-01-09 at 11:44, zzapper wrote: On Fri, 9 Jan 2004 20:40:44 +0530, "Nitin Mehta" <[EMAIL PROTECTED]> wrote: >you need not write any scripts, just use MySQL Front to Import/Export dat from M$ Access Are you using MySqlfront2.5 (the original) or the new 3.x? BTW I already use/love 2.5. hey hey hey! wait a second there! is there a new mysqlfront? :o where can i download it? :) 3.0??? I already love and worship mysqlfront 2.5, i thought it was dead :( do you have a link? Thanxs in advance! -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 | ||geek by nature - linux by choice | |...| Best Regards David Rayner MSc CEng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting an Access Table to MySQL
thanxss! :) On Fri, 2004-01-09 at 12:04, zzapper wrote: > On Fri, 09 Jan 2004 11:48:42 -0400, Victor Medina <[EMAIL PROTECTED]> > wrote: > > > > > > >hey hey hey! wait a second there! is there a new mysqlfront? :o where > >can i download it? :) 3.0??? I already love and worship mysqlfront 2.5, > >i thought it was dead :( > >do you have a link? > > Victor, > It's at the same URL www.mysqlfront.de I'm not sure it's stable yet > > > > > 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 -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 | ||geek by nature - linux by choice | |...|
Re: Converting an Access Table to MySQL
On Fri, 09 Jan 2004 11:48:42 -0400, Victor Medina <[EMAIL PROTECTED]> wrote: > >hey hey hey! wait a second there! is there a new mysqlfront? :o where >can i download it? :) 3.0??? I already love and worship mysqlfront 2.5, >i thought it was dead :( >do you have a link? Victor, It's at the same URL www.mysqlfront.de I'm not sure it's stable yet 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]
Re: Converting an Access Table to MySQL
On Fri, 2004-01-09 at 11:44, zzapper wrote: > On Fri, 9 Jan 2004 20:40:44 +0530, "Nitin Mehta" > <[EMAIL PROTECTED]> wrote: > > >you need not write any scripts, just use MySQL Front to Import/Export dat from M$ > >Access > > Are you using MySqlfront2.5 (the original) or the new 3.x? > > BTW I already use/love 2.5. hey hey hey! wait a second there! is there a new mysqlfront? :o where can i download it? :) 3.0??? I already love and worship mysqlfront 2.5, i thought it was dead :( do you have a link? Thanxs in advance! -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 | ||geek by nature - linux by choice | |...|
Re: Converting an Access Table to MySQL
On Fri, 9 Jan 2004 20:40:44 +0530, "Nitin Mehta" <[EMAIL PROTECTED]> wrote: >you need not write any scripts, just use MySQL Front to Import/Export dat from M$ >Access Are you using MySqlfront2.5 (the original) or the new 3.x? BTW I already use/love 2.5. That's why I post to Usenet to be told there's a better way; thanx. The only advantage of my script is that I could do a fairly complex query to "transform" the data if that were necessary 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]
RE: Converting an Access Table to MySQL
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;
> |;
>
> # MMDDHHMMSS
> ($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;
> }
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;
> |;
>
> # MMDDHHMMSS
> ($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)
Converting an Access Table to MySQL
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;
|;
# MMDDHHMMSS
($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 &
