• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

import data from access to mysql

 
hanumanth reddy
Ranch Hand
Posts: 118
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
can any body help me in importing data from access dtabase to mysql clearly

thanks in advance
 
John Bateman
Ranch Hand
Posts: 320
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
Now I want to mention that I am assuming you are using mysql on a unix box.
I would do it by dumping the Access tables into a comma delimited list, then using a small java app (servlet even) to open the comma delimited file and input the information into the mysql table. (You can use a "Reader" and a "Tokenizer" to accomplish this easily.)
A second option is to select all the information from each Access table and create an INSERT statement from it, store this info into a file (INSERTS.txt).. Then all you have to do is issue a "mysql <database_name> << INSERTS.txt" at your prompt. (This way would require come 'intermediate' application quering the database and constructing the INSERT commands).
I have used both ways and I found the comman delimited list was the easiest. Almost everything can be exported into a comma delimited list.
Make sure that when you create the mysql table you do not enable identity fields or primary keys. Add those later with the ALTER TABLE command. This will allow you to keep referential integrity while tranferring from Access to Mysql as it will allow you to do what's called an 'identity insert'.
Hope this helped.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic