Software designed to convert Microsoft Access MDB files to a format suitable for importing into a MySQL database.
MDB2MySQL README
====================================
October 14, 2004
Created By: Bill Lewis - Enobis (www.enobis.com)
Latest Version: v1.0
Available At: http://www.enobis.com/sw/mdb2mysql/
This is free software; you can redistribute it and/or modify it under
the terms of the GNU General Public License as published by the Free
Software Foundation - visit: http://www.gnu.org/copyleft/gpl.html
==================
INTRODUCTION
==================
Plain and simple, this program was created to produce an SQL format
comparable to mysqldump for converting Microsoft Access databases to
MySQL.
==================
MOTIVATION
==================
MDB2MySQL was originally created for internal use for converting our client's
Microsoft Access (.mdb) files to a form suitable for porting to the MySQL
relational database. The program is a Perl script, written to work in
conjunction with MDB Tools (mdbtools.sourceforge.net), by taking the
information contained within the MDB file and converting it into a format
similar to that obtained by dumping a MySQL database.
The output of MDB2MySQL is written to standard out and can be either
redirected to a file, or piped directly into MySQL using the mysql client.
The script is capable of exporting table schema, data with suitable inserts,
and a combination of both. All Microsoft Access file types are converted
into corresponding, suitable, MySQL field types.
Doesn't MDB Tools already do this? Well, yes and no. While MDB Tools is
an awesome tool for extracting information from MDB files, the info still
needed to be heavily manipulated before it was capable for importing into
MySQL. In particular, the field types needed to be converted to suitable
MySQL field types. Given that our client's databases often had numerous
tables and database sizes of 50 to upwards of 100 MBytes, this made the task
of hand manipulation daunting. Thus we looked to scripting the process, and
MDB2MySQL was born.
==================
REQUIREMENTS
==================
Given that MDB2MySQL is a Perl script, the first obvious requirement is Perl.
Which can be obtained at:
http://www.perl.com/ or http://www.cpan.org/
Secondly, the script requires MDB Tools version 0.5 for extracting the
information from the MDB file. Which can be obtained from:
http://mdbtools.sourceforge.net/
This program has been tested on Linux machines, using Perl v5.8.4. I
suspect the limitation here is going to be MDB Tools, so if you can build
MDB Tools on a different flavor of Unix, then I suspect MDB2MySQL should
work without difficulty.
==================
USAGE
==================
Currently there are no man pages for the program, and not sure there will be
anytime soon, so consider this file the manual for the time being. Most of
the options should be self explanatory, with a few exceptions.
Usage: mdb2mysql [options] <mdb file>
-c Create table structure only, no data.
-d Add a 'drop table' before each create.
-e Use the much faster, extended INSERT syntax.
-i Export data inserts only.
-l Add locks around insert statements.
-o <tables> Omit tables in this comma seperated list.
-r <character> Replace illegal characters with given character.
The default character is an underscore.
-t <tables> Export only this list of comma seperated tables.
-u Report unknown Access data type and exit.
-x Same as using -d -e -l combined options.
-U <type> Use the MySQL data type for unknown Access types.
Unless given, 'blob' will be used by default.
-h, --help This message and exit.
-V, --version Output version information and exit.
Further Explanations:
-r <character>
Microsoft Access allows characters other than alphanumeric ones,
including spaces, to be used as field names, which are illegal in
MySQL. This option allows you to change the default underscore that
is used in replacement of these illegal characters, to a character of
your choosing. Please refer to the MySQL manual for a list of legal
characters to be used for field names.
-x
This option produces a format similar to using the --opt option with
mysqldump.
This program has been successfully used to convert MDB files for both the
JET3 (Access 97) and JET4 (Access 2000 & XP) versions, as well as files as
small as 500 KB to as large as 125 MB. While not tested, larger files should
work as well.
==================
DATA TYPES
==================
The following is a list of the known Access Data Types and the corresponding
MySQL data type used for conversion by the program:
Access Data Type MySQL Data Type
-------------------- --------------------
Text if less than 2 characters a char otherwise a varchar
of corresponding length.
Memo/Hyperlink text
Byte tinyint
Integer smallint
LongInteger int
Single double
Double double
Numeric float
Currency decimal(10,2)
DateTime datetime
Boolean enum('1','0')
ReplicationID tinyblob
OLE longblob
Any unknown Access data type will be converted to a blob, unless indicated
otherwise by using the -U option.
==================
BUGS & ISSUES
==================
There are currently no known bugs or issues, however, that doesn't mean there
aren't any. Please report any bugs or issues to Bill on-line at:
http://www.enobis.com/contact/
==================
LEGAL STUFF
==================
This program was written by Bill Lewis and is copyrighted (C) 2004. You may
do whatever you like with the source code, provided that you continue to
acknowledge the author in any distribution of it, and of any distribution of
code substantially derived from it; and that you make the original source code
available. Also, please include this README file along with any distribution.
Needless to say, there is no warranty of any kind, and you use this at your
own risk.