Import Access into MySQL

[Bruce A. Julseth]

Newbie Question:

Is there a way to import MS Access into MySQL? Maybe create a CSV or
something. If so, what would be the SQL to do this?

Thanks...

Bruce

[MeerKat]

Re: Import Access into MySQL

Bruce A. Julseth wrote:
[color=blue]
> Newbie Question:
>
> Is there a way to import MS Access into MySQL? Maybe create a CSV or
> something. If so, what would be the SQL to do this?[/color]

Try a MySQL group.
[color=blue]
> Thanks...
>
> Bruce
>
>[/color]

--
MeerKat

[Marcel]

Re: Import Access into MySQL


"Bruce A. Julseth" <bruceaj@attglo bal.net> schreef in bericht
news:3f711bae_3 @news1.prserv.n et...[color=blue]
> Newbie Question:
>
> Is there a way to import MS Access into MySQL? Maybe create a CSV or
> something. If so, what would be the SQL to do this?
>
> Thanks...
>
> Bruce
>
>[/color]

From MS Access you can export tabels as CSV .... this CSV can be imported
in MySQL ..... if you have a MySQL interface installed (like phpMyAdmin)
it's very easy to import a CSV into your MySQL, if not, you can write your
own statement. Therefore have a look at the MySQL online docuemntation and
have look at load data in file

Marcel

[Geoff Berrow]

Re: Import Access into MySQL

I noticed that Message-ID:
<I6bcb.3752$Gc5 .33351723@news-text.cableinet. net> from MeerKat contained
the following:
[color=blue][color=green]
>> Is there a way to import MS Access into MySQL? Maybe create a CSV or
>> something. If so, what would be the SQL to do this?[/color]
>
>Try a MySQL group.[/color]

But there is a php answer. I found this bit of code which works. (though
I note that mysql_db_query( ) is deprecated.

I export from access using a semicolon as separator save it as query.txt

then run this.

<?php


# MySQL database User ID, Password and DB name
$sql_id = "userid";
$sql_pwd = "pwd";
$sql_db = "database_name" ;
$table = "table_name ";
$infile = "query.txt" ;

# Connect to the database
mysql_connect(' mysqldb.gradwel l.net',$sql_id, $sql_pwd);

# Delete the current content of the table
$result = mysql_db_query( $sql_db,"DELETE FROM $table") or die ("Invalid
DELETE query");

# Optimize the current table (recover empty space)
$result = mysql_db_query( $sql_db,"OPTIMI ZE TABLE $table") or die
("Invalid OPTIMIZE query");

# Load local comma separated, fields enclosed by quotes text database -
File has to be in the same directory of this file
$result = mysql_db_query( $sql_db,"LOAD DATA LOCAL INFILE '$infile' INTO
TABLE $table FIELDS TERMINATED BY ';' ENCLOSED BY ''") or die ("Invalid
DATA LOAD query");

# Get how many records are present in the table now
$result = mysql_db_query( $sql_db,"SELECT * from $table") or die
("Invalid SELECT query");
$rows_count = mysql_num_rows( $result);

echo "Records: $rows_count"; mysql_free_resu lt($result);

?>

--
Geoff Berrow
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Peter Binderup]

Re: Import Access into MySQL

try this free program: http://www.zebradb.de/mdbtomysql/

/Peter

[Bruce A. Julseth]

Re: Import Access into MySQL

Question 1: How can you tell that "mysql_db_query () is deprecated"?

Question 2: I run the .php and get the mysql_error() on the

$sql = "LOAD DATA LOCAL INFILE '$infile' INTO TABLE $table FIELDS TERMINATED
BY ',' ENCLOSED BY \"\"";

$result = mysql_db_query( $sql_db, $sql) or die (mysql_error()) ;

I get the error: "The used command is not allowed with this MySQL version"

The constructed SQL looks like:
LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE Serenade FIELDS TERMINATED BY
',' ENCLOSED BY ""

I run this command at a mysql command line and it works.

Why???

Bruce


"Geoff Berrow" <bl@ckdog.co.uk .the.cat> wrote in message
news:u3m2nvcnf1 04qdhkrncdtmufh chiruhr12@4ax.c om...[color=blue]
> I noticed that Message-ID:
> <I6bcb.3752$Gc5 .33351723@news-text.cableinet. net> from MeerKat contained
> the following:
>[color=green][color=darkred]
> >> Is there a way to import MS Access into MySQL? Maybe create a CSV or
> >> something. If so, what would be the SQL to do this?[/color]
> >
> >Try a MySQL group.[/color]
>
> But there is a php answer. I found this bit of code which works. (though
> I note that mysql_db_query( ) is deprecated.
>
> I export from access using a semicolon as separator save it as query.txt
>
> then run this.
>
> <?php
>
>
> # MySQL database User ID, Password and DB name
> $sql_id = "userid";
> $sql_pwd = "pwd";
> $sql_db = "database_name" ;
> $table = "table_name ";
> $infile = "query.txt" ;
>
> # Connect to the database
> mysql_connect(' mysqldb.gradwel l.net',$sql_id, $sql_pwd);
>
> # Delete the current content of the table
> $result = mysql_db_query( $sql_db,"DELETE FROM $table") or die ("Invalid
> DELETE query");
>
> # Optimize the current table (recover empty space)
> $result = mysql_db_query( $sql_db,"OPTIMI ZE TABLE $table") or die
> ("Invalid OPTIMIZE query");
>
> # Load local comma separated, fields enclosed by quotes text database -
> File has to be in the same directory of this file
> $result = mysql_db_query( $sql_db,"LOAD DATA LOCAL INFILE '$infile' INTO
> TABLE $table FIELDS TERMINATED BY ';' ENCLOSED BY ''") or die ("Invalid
> DATA LOAD query");
>
> # Get how many records are present in the table now
> $result = mysql_db_query( $sql_db,"SELECT * from $table") or die
> ("Invalid SELECT query");
> $rows_count = mysql_num_rows( $result);
>
> echo "Records: $rows_count"; mysql_free_resu lt($result);
>
> ?>
>
> --
> Geoff Berrow
> It's only Usenet, no one dies.
> My opinions, not the committee's, mine.
> Simple RFDs http://www.ckdog.co.uk/rfdmaker/[/color]

[Geoff Berrow]

Re: Import Access into MySQL

I noticed that Message-ID: <3f7175f5_1@new s1.prserv.net> from Bruce A.
Julseth contained the following:
[color=blue]
>Question 1: How can you tell that "mysql_db_query () is deprecated"?[/color]
Because it says so in the manual.[color=blue]
>
>Question 2: I run the .php and get the mysql_error() on the
>
>$sql = "LOAD DATA LOCAL INFILE '$infile' INTO TABLE $table FIELDS TERMINATED
>BY ',' ENCLOSED BY \"\"";
>
>$result = mysql_db_query( $sql_db, $sql) or die (mysql_error()) ;
>
>I get the error: "The used command is not allowed with this MySQL version"[/color]

I remember I had a few problems getting it to work which is why I ended
up using the semi colon. But this sounds more like a problem with the
version of MySQL. Obviously your other queries are working.

--
Geoff Berrow
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[David Robley]

Re: Import Access into MySQL

In article <3f711bae_3@new s1.prserv.net>, bruceaj@attglob al.net says...[color=blue]
> Newbie Question:
>
> Is there a way to import MS Access into MySQL? Maybe create a CSV or
> something. If so, what would be the SQL to do this?
>
> Thanks...[/color]

Doesn't Access have a facility to export a csv? Once you have a csv, you
should be able to do something with MySQLs LOAD DATA INFILE

Cheers
--
Quod subigo farinam

$email =~ s/oz$/au/o;
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet?

[Fli7e]

Re: Import Access into MySQL

Simply use ODBC and transfer Access Data > MySQL on the fly.

"David Robley" <robleyd[spam]@ozemail.com.au > schrieb im Newsbeitrag news:MPG.19dc39 e0b53e6de19896d a@news.spiderwe b.com.au...[color=blue]
> In article <3f711bae_3@new s1.prserv.net>, bruceaj@attglob al.net says...[color=green]
> > Newbie Question:
> >
> > Is there a way to import MS Access into MySQL? Maybe create a CSV or
> > something. If so, what would be the SQL to do this?
> >
> > Thanks...[/color]
>
> Doesn't Access have a facility to export a csv? Once you have a csv, you
> should be able to do something with MySQLs LOAD DATA INFILE
>
> Cheers
> --
> Quod subigo farinam
>
> $email =~ s/oz$/au/o;
> A: Because it messes up the order in which people normally read text.
> Q: Why is top-posting such a bad thing?
> A: Top-posting.
> Q: What is the most annoying thing on usenet?[/color]

[RonS]

Re: Import Access into MySQL

Yes ... this is the way I transferred my Access data into
MySQL. Very easy.

Fli7e wrote:
[color=blue]
> Simply use ODBC and transfer Access Data > MySQL on the fly.[/color]

[Thomas]

Re: Import Access into MySQL


<snip>
[color=blue][color=green]
> >$sql = "LOAD DATA LOCAL INFILE '$infile' INTO TABLE $table FIELDS[/color][/color]
TERMINATED[color=blue][color=green]
> >BY ',' ENCLOSED BY \"\"";[/color][/color]

I had a similar problem the other day on my Windows machine. It was solved
by removing LOCAL from the query.

Good luck,
Thomas.

[george]

Re: Import Access into MySQL

"Bruce A. Julseth" <bruceaj@attglo bal.net> wrote in message news:<3f711bae_ 3@news1.prserv. net>...[color=blue]
> Newbie Question:
>
> Is there a way to import MS Access into MySQL? Maybe create a CSV or
> something. If so, what would be the SQL to do this?
>[/color]

YES
Incredibly simple. Been using it for 2 years now for several
databases.
SQLion is designed to import and export between MS Access (and Excel
and so on ) and MYSQL (and so on)

I click import, choose the Access db, choose the table and click
exceute (get on with it) == If I sound impressed I am, and I'm always
amazed that noone seems to have heard of it.

Anyways, go here: www.waiheke.co.nz/php/

seeya