[Techtalk] MS Access --> mysql

[Jacinta Richardson]

On Fri, 2 May 2003, Phil Savoie wrote:

> All,
> 
> My boss had asked if there was a way to convert an ms access db to
> mysql.  Not having dealt with databases before I couldn't give a
> qualified answer.  So I am calling on all those db experts out there to
> ask a few questions. 
> 
> 1.	Is it as simple as exporting in some sort of delimitied file and importing 
> into mysql?

That is certainly one way (and a very common way) of doing it.

> 2. 	Has anyone ever tried to do this?

Yup.  Many times.  Although I was importing into postgresql rather than
mysql, but it's effectively the same thing.  (Well, much the same as in
being almost identical when it comes to inserting stuff).  Not implying
that the dbs are the same, because they most certainly aren't).

> 3.	Were there any gotchas to look out for?

Yup.  Check and double check that your delimiter is either not in your
data or is properly escaped.  Then go check it again.  It's amazing how
much pain strings like this:
O'Hara
Level 2, 512 Elizabeth St
James "Jimmy" Jackson
104 - 107 Elsternwich ST	Coburg	Vic	3333

(note the tabs in that last one)

etc can cause.  (Oh, and watch out for binary data stored in the db too.
Mysql will support it, but it can get horribly corrupted in the
conversion!) Don't count on tabs to not be in your data either.
What I usually do is dump into a CSV file then write a Perl script to
validate the data.  Suspicious rows are flagged and the good rows are
inserted into the db.

If you need any help with such a script, feel free to ask.

> 4.	Is msql relatively close to ms access in behaviour?

Not sure.  Mysql (as opposed to the horrible msql) is a relational
database.  Since I've had next to nothing to do with Access I can't really
say.  Mysql is just a database, just somewhere to store data.  I've always
expected Access - on the other hand - to be very GUI based, so it depends
on your expected "behaviour".

I ___think___ that sql queries to an Access db would largely work on mysql
however.  (You might have to do a little bit of conversion for date
handling and such).

All the best,

	Jacinta

--
   ("`-''-/").___..--''"`-._          |  Jacinta Richardson	    |
    `6_ 6  )   `-.  (     ).`-.__.`)  |  Perl Training Australia    |
    (_Y_.)'  ._   )  `._ `. ``-..-'   |      +613 9354 6001 	    |  
  _..`--'_..-_/  /--'_.' ,'           | contact at perltraining.com.au |
(il),-''  (li),'  ((!.-'              |   www.perltraining.com.au   |