[转]Linux之中MS Access MDB到MYSQL的转换

[linforest]

MS Access MDB to MYSQL Conversion on Linux:
文章来源：http://www.opengurukul.com/vlc/mod/page ... hp?id=2675


Install package mdbtools that has mdb-schema and mdb-export,
工具开发者: http://mdbtools.sourceforge.net/
安装：sudo apt-get install mdbtools
工具软件中心亦有： MDB File Viewer

The mdb-schema can be use to see schema.
The mdb-tables can be used to see tables in it

/usr/bin/mdb-array
/usr/bin/mdb-export
/usr/bin/mdb-header
/usr/bin/mdb-hexdump
/usr/bin/mdb-parsecsv
/usr/bin/mdb-prop
/usr/bin/mdb-schema
/usr/bin/mdb-sql
/usr/bin/mdb-tables
/usr/bin/mdb-ver

To get the list of tables, you run the following command:

mdb-tables database.mdb


You can then get a CSV version for each table using:

mdb-export database.mdb table_name


You can also convert the mdb into a format required by MySQL. First you must get the put the table schema into the database using the following command:

mdb-schema database.mdb | mysql -u username -p database_name


You then import each table by running:

mdb-export -I database.mdb table_name | sed -e 's/)$/)\;/' | mysql -u username -p database_name



Script to convert .mdb file to MySQL:
===========================


$ cat ~/bin/mdb2mysql.sh
#!/bin/sh


if [ $# -ne 4 ]; then
echo "usage: $0 msaccess.mdb mysqldb mysqluser mysqlpass"
exit
fi

MDB_FILE=$1
MYSQL_DBNAME=$2
MYSQL_USER=$3
MYSQL_PASS=$4

MYSQL_IMPORT=/tmp/`basename $MDB_FILE .mdb`.sql

>$MYSQL_IMPORT

# create database
echo "DROP DATABASE IF EXISTS $MYSQL_DBNAME;" >> $MYSQL_IMPORT
echo "CREATE DATABASE $MYSQL_DBNAME; " >> $MYSQL_IMPORT
echo "USE $MYSQL_DBNAME; " >> $MYSQL_IMPORT

# import table structures with mysql data types
mdb-schema -S $MDB_FILE mysql >> $MYSQL_IMPORT

perl -p -i -e 's/-----*/--/g' $MYSQL_IMPORT
perl -p -i -e 's/DROP TABLE (.*)/DROP TABLE IF EXISTS $1/gi' $MYSQL_IMPORT

# Fix the Variables
#perl -p -i -e 's/Text/VARCHAR/g' $MYSQL_IMPORT
#perl -p -i -e 's/Long Integer/INT\(11\)/g' $MYSQL_IMPORT

# import data
for TABLE in `mdb-tables $MDB_FILE`
do
mdb-export -R';' -I $MDB_FILE $TABLE >> $MYSQL_IMPORT

done

mysql -u$MYSQL_USER -p$MYSQL_PASS < $MYSQL_IMPORT

if [ $? -ne 0 ]; then
echo ""
echo "Fix the script at $MYSQL_IMPORT"
echo ""
echo "Run it using following command"
echo "mysql -u$MYSQL_USER -p$MYSQL_PASS < $MYSQL_IMPORT"
else
echo ""
echo "DONE. Script used is: $MYSQL_IMPORT"
echo ""
echo "Remove it if you no longer need it"
fi
$

Example:
=======


$ mdb2mysql.sh
usage: mdb2mysql.sh msaccess.mdb mysqldb mysqluser mysqlpass
$


$ mdb2mysql.sh PayRoll.mdb payroll root root

DONE. Script used is: /tmp/PayRoll.sql

Remove it if you no longer need it
$

ACCESS 注意事项:
============

MS Access allows +, - and % also in the column name. Such things can cause confusion.... the tool replaces + and - with just underscore...

If you had fields like Adj+ and Adj-, both will be named as Adj_ , this is a problem.

The mdb-schema will replace Discount% with Discount_ but INSERT INTO queries may still have DISCOUNT%.

Be careful.
Last modified: Friday, 16 March 2012, 10:59 PM
Site front pageFirst activity in course: News forumPrevious activity: phpMyAdminNext activity: Popular ToolsLast activity in course: Popular Tools


MDB File Viewer界面：

[linforest]

上述脚本确实可以将ACCESS数据库的schema顺利导入MySQL（没有逐个字段地去核对字段定义是否忠实），但不知道为什么不能把各个表里面的具体数据导入MySQL。

[linforest]

日期时间字段的导出形式（“2014年07月04日 16时04分43秒”），在导入MySQL的时候可能有些麻烦。

[linforest]

笨办法：先在MySQL把相应的空表建好，相应的datetime字段先改为Text类型，这样可以先把日期数据（如“2014年07月04日 16时04分43秒”）导入进来；然后在采用字符串函数表达式去更新该Text字段，改成便于MySQL识别的格式（如2014/07/04 16:04:43”）；之后在将字段类型改回去datetime类型。

笨的原因：主要是所要导入的数据库之中的表比较多（40张左右），每张表的字段也不少（几个到几十个），采用str_to_date的方法显得麻烦些，因为不愿意麻烦去每张表逐个字段地罗列在LOAD DATA LOCAL INFILE语句里面，并且还要为str_to_date函数设置变量和写表达式。

[linforest]

MDB向MySQL之中导入数据库Schema（空表结构）是上述基于mdbtools的脚本mdb2mysql.sh负责完成的。MDB之中各个表的实际数据是采用MDB File Viewer逐个表导出为CSV的。每张CSV分别采用MDB之中各表的名称（注意大小写）+“.csv”。