By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,474 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,474 IT Pros & Developers. It's quick & easy.

Import Access into MySQL

P: n/a
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
Jul 17 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
Bruce A. Julseth wrote:
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?
Try a MySQL group.
Thanks...

Bruce


--
MeerKat

Jul 17 '05 #2

P: n/a

"Bruce A. Julseth" <br*****@attglobal.net> schreef in bericht
news:3f********@news1.prserv.net...
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


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
Jul 17 '05 #3

P: n/a
I noticed that Message-ID:
<I6*********************@news-text.cableinet.net> from MeerKat contained
the following:
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?


Try a MySQL group.


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.gradwell.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,"OPTIMIZE 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_result($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/
Jul 17 '05 #4

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

/Peter
Jul 17 '05 #5

P: n/a
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:u3********************************@4ax.com...
I noticed that Message-ID:
<I6*********************@news-text.cableinet.net> from MeerKat contained
the following:
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?


Try a MySQL group.


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.gradwell.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,"OPTIMIZE 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_result($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/

Jul 17 '05 #6

P: n/a
I noticed that Message-ID: <3f********@news1.prserv.net> from Bruce A.
Julseth contained the following:
Question 1: How can you tell that "mysql_db_query() is deprecated"? Because it says so in the manual.
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"


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/
Jul 17 '05 #7

P: n/a
In article <3f********@news1.prserv.net>, br*****@attglobal.net says...
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...


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?
Jul 17 '05 #8

P: n/a
Simply use ODBC and transfer Access Data > MySQL on the fly.

"David Robley" <robleyd[spam]@ozemail.com.au> schrieb im Newsbeitrag news:MP************************@news.spiderweb.com .au...
In article <3f********@news1.prserv.net>, br*****@attglobal.net says...
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...


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?

Jul 17 '05 #9

P: n/a
Yes ... this is the way I transferred my Access data into
MySQL. Very easy.

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


Jul 17 '05 #10

P: n/a

<snip>
$sql = "LOAD DATA LOCAL INFILE '$infile' INTO TABLE $table FIELDS TERMINATEDBY ',' ENCLOSED BY \"\"";


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

Good luck,
Thomas.
Jul 17 '05 #11

P: n/a
"Bruce A. Julseth" <br*****@attglobal.net> wrote in message news:<3f********@news1.prserv.net>...
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?


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
Jul 17 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.