linux mint で .mdb データを読み込む

いわゆる MS Access のデータベースは linux で開こうとするととても難しく、これまでは完全にはできませんでした。
しかし、最近になってやっとわかったのでメモします。

.mdb という拡張子を持つ MS のデータベースはとても特殊で、特にメモ型というタイプのフィールドを読み込むことができませんでした。

しかし以下のようにすれば完全に .mdb のデータを mysql に移行することができます。

環境
mdbtools のインストール
.mdb のテーブルをエクスポート
テーブル作成
シェルスクリプトで実行
データベース名やカラム名を変更

環境

linux mint 21
mysql  Ver 8.0.30-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

こんな構造です。

~/mmr
├── mdb2mysql.sh
├── mmr.mdb
└── msqlset.ddl

mdbtools のインストール

まずは mdbtools をインストール。

sudo apt install mdbtools

データベースの構造を確認します。

mdb-schema mmr.mdb

以下のように出力されます。

-- ----------------------------------------------------------
-- MDB Tools - A library for reading MS Access database files
-- Copyright (C) 2000-2011 Brian Bruns and others.
-- Files in libmdb are licensed under LGPL and the utilities under
-- the GPL, see COPYING.LIB and COPYING files respectively.
-- Check out http://mdbtools.sourceforge.net
-- ----------------------------------------------------------

-- That file uses encoding UTF-8

CREATE TABLE [mdb_info]
 (
	[mi_id]			Long Integer,
	[mi_revision]			DateTime,
	[mi_version]			Text (10),
	[mi_comment]			Text (100)
);

CREATE TABLE [session_info]
 (
	[si_mdb_id]			Long Integer,
	[si_patient_id]			Text (50),
	[si_patient_bday]			Text (50),
	[si_patient_name]			Text (50),
	[si_patient_reading]			Text (50),
	[si_patient_sex]			Text (50),
	[si_department]			Text (50),
	[si_treatment]			Text (50),
	[si_sickness]			Text (50),
	[si_doctor]			Text (50),
	[si_comment]			Memo/Hyperlink (255),
	[si_path]			Text (50),
	[si_session_start]			Text (16) NOT NULL,
	[date_added]			DateTime NOT NULL,
	[date_rev]			DateTime,
	[flag_delete]			Byte,
	[flag_lock]			Byte,
	[mov_copy]			Byte,
	[snp_copy]			Byte,
	[scene_copy]			Byte,
	[opinion_copy]			Byte,
	[copy_strage]			Text (255),
	[copy_date]			Text (255),
	[dcm_export_rec]			Byte,
	[dcm_export_main]			Byte,
	[dcm_export_view]			Byte,
	[machine_uid]			Text (255)
);

2つのテーブルのうちで session_info というテーブルを mysql に移行します。

しかし、このクエリを mysql 上で実行してもエラーになります。

.mdb のテーブルをエクスポート

mmr.mdb の session_info をエクスポートします。

mdb-export -D "%Y-%m-%d %H:%M:%S" -H -I mysql mmr.mdb session_info > mmr.sql

37,000 行・27項目のデータが一瞬でエクスポートされます。

テーブル作成

mysql にログイン。

mysql -u root -p

データベースを作成します。

mysql> CREATE DATABASE mmr;

データベース変更。

mysql> USE mmr;

最初のテーブル情報から以下のようなクエリを作成してテーブルを作成します。

mysql> CREATE TABLE `session_info`
 (
	`si_mdb_id` INT,
	`si_patient_id` varchar(50),
	`si_patient_bday` varchar(50),
	`si_patient_name` varchar(50),
	`si_patient_reading` varchar(50),
	`si_patient_sex` varchar(50),
	`si_department` varchar(50),
	`si_treatment` varchar(50),
	`si_sickness` varchar(50),
	`si_doctor` varchar(50),
	`si_comment` varchar(255),
	`si_path` varchar(50),
	`si_session_start` varchar(16) NOT NULL,
	`date_added` DateTime NOT NULL,
	`date_rev` DateTime,
	`flag_delete` varchar(100),
	`flag_lock` varchar(100),
	`mov_copy` varchar(100),
	`snp_copy` varchar(100),
	`scene_copy` varchar(100),
	`opinion_copy` varchar(100),
	`copy_strage` varchar(255),
	`copy_date` varchar(255),
	`dcm_export_rec` varchar(100),
	`dcm_export_main` varchar(100),
	`dcm_export_view` varchar(100),
	`machine_uid` varchar(255)
);

データのインポート。

mysql> source mmr.sql;

シェルスクリプトで実行

テーブル情報だけは手動でおこなう必要がありますが、プロセスが面倒なのでテーブル以外を自動的に実行します。

mdb2mysql.sh

#!/bin/sh
cd ~/mmr
mdb-export -D "%Y-%m-%d %H:%M:%S" -H -I mysql mmr.mdb session_info > mmr.sql
mysql < ~/mmr/msqlset.ddl

mysqlset.ddl

DROP DATABASE IF EXISTS mmr;
CREATE DATABASE mmr;
USE mmr;
CREATE TABLE `session_info`
 (
	`si_mdb_id`			INT,
	`si_patient_id`			varchar(50),
	`si_patient_bday`			varchar(50),
	`si_patient_name`			varchar(50),
	`si_patient_reading`			varchar(50),
	`si_patient_sex`			varchar(50),
	`si_department`			varchar(50),
	`si_treatment`			varchar(50),
	`si_sickness`			varchar(50),
	`si_doctor`			varchar(50),
	`si_comment`			varchar(255),
	`si_path`			varchar(50),
	`si_session_start`			varchar(16) NOT NULL,
	`date_added`			DateTime NOT NULL,
	`date_rev`			DateTime,
	`flag_delete`			varchar(100),
	`flag_lock`			varchar(100),
	`mov_copy`			varchar(100),
	`snp_copy`			varchar(100),
	`scene_copy`			varchar(100),
	`opinion_copy`			varchar(100),
	`copy_strage`			varchar(255),
	`copy_date`			varchar(255),
	`dcm_export_rec`			varchar(100),
	`dcm_export_main`			varchar(100),
	`dcm_export_view`			varchar(100),
	`machine_uid`			varchar(255)
);
SOURCE mmr.sql;

データベース名やカラム名を変更

python で作ったプログラムでは、データベース名：es、テーブル名：mmr、フィールド名もオリジナルとかなり異なっているのでそれらを変更します。

mdb2mysql.sh を以下のように変更。

mdb2mysql.sh

#!/bin/sh

cd ~/MMR
mdb-export -D "%Y-%m-%d %H:%M:%S" -H -I mysql mmr.mdb session_info > MMR.sql
mysql < ~/MMR/msqlset.ddl
mysql < ~/MMR/rename.ddl

rename.ddl を追加します。

rename.ddl

USE MMR;
ALTER TABLE session_info CHANGE COLUMN si_mdb_id studyID VARCHAR(100) NULL;
ALTER TABLE session_info CHANGE COLUMN si_patient_id karteNo VARCHAR(100) NULL;
ALTER TABLE session_info CHANGE COLUMN si_patient_bday birthDate VARCHAR(100) NULL;
ALTER TABLE session_info CHANGE COLUMN si_patient_name ptName VARCHAR(100) NULL;
ALTER TABLE session_info CHANGE COLUMN si_patient_sex gender VARCHAR(100) NULL;
ALTER TABLE session_info CHANGE COLUMN si_department comment VARCHAR(100) NULL;
ALTER TABLE session_info CHANGE COLUMN si_treatment proced VARCHAR(100) NULL;
ALTER TABLE session_info CHANGE COLUMN si_sickness patho VARCHAR(100) NULL;
ALTER TABLE session_info CHANGE COLUMN si_doctor doctor VARCHAR(100) NULL;
ALTER TABLE session_info CHANGE COLUMN si_comment descript VARCHAR(100) NULL;
ALTER TABLE session_info CHANGE COLUMN si_session_start path VARCHAR(100) NULL;
ALTER TABLE session_info CHANGE COLUMN date_added studyDate VARCHAR(100) NULL;
ALTER TABLE session_info ADD COLUMN modality VARCHAR(100) NULL;

DROP DATABASE IF EXISTS es;
CREATE DATABASE es;
RENAME TABLE MMR.session_info TO es.mmr;

DROP DATABASE MMR;

このシェルスクリプトが動くためには、/etc/mysql/my.cnfの最後に以下を追加する必要があります。

/etc/mysql/my.cnf

[client]
user = root
password = pass