MDB-Tools, SQLite/Spatialite (PostgreSQL,...) ou comment traiter les bases de données Microsoft Access (mdb) pour les autres...

Vous êtes sur Linux ou Mac OS X et vous recevez un fichier .mdb (Microsoft Access) et vous ne savez pas comment l'ouvrir, surtout qu'il y a peut-être des données spatiales. Que faire ? Se résigner en râlant sur ... (comme moi ) ? Et bien non.

C'est là que peuvent intervenir les MDB Tools. C'est un ensemble d'utilitaires qui permettent d'accéder à des bases Microsoft Access de type Jet 3 et Jet 4 (Access 97, 2000/2002). Ils permettent non seulement d'interroger ces bases en SQL mais de les exporter vers d'autres systèmes comme Oracle, PostgreSQL, MySQL, Sybase et même SQLite. Le travail se fait en ligne de commandes ou avec une interface graphique basée sur GTK, gmdb2. Sur Mac OS X, Jacob Egger a récemment créé MDB Viewer (4,99 € sur l'App Store), belle interface graphique pour MDB Tools.

Les utilitaires disponibles sont :

gmdb2, mdb-array, mdb-export, mdb-header, mdb-hexdump, mdb-import, mdb-parsecsv, mdb-prop, mdb-schema, mdb-sql, mdb-tables, mdb-ver, prcat, prdata, prdump, prindex, prkkd, prole, prtable, unittest, updrow

Mais ils sont mal documentés et, hormis les plus importants, il n'est souvent pas facile de les utiliser. De plus, le développement semble s'être arrêté depuis la dernière version (0.6, mais 0.6.x est disponible en cvs). Cette dernière est pourtant pleinement fonctionnelle et est utilisée dans d'autres projets comme Kexi (www.kexi-project.org/) ou GDAL (en partie) pour lire les « ESRI Personal GeoDatabase »  (www.gdal.org/ogr/drv_pgeo.html).

Les utilitaires les plus importants pour la migration de bases Microsoft Access sont :

• mdb-schema : pour exporter les schémas en SQL (LDD ou DDL) ;
• mdb-tables : pour lister les tables présentes dans une base ;
• mdb-export : pour exporter des tables au format CSV ou SQL (INSERT) ;
• mdb-sql : pour interroger une base en SQL.
• (mdb-ver : version JET d'une base (3 ou 4)) 

Les bases Microsoft Access ne respectent pas tous les principes des bases de données relationnelles. Ainsi à la fin d'un mdb-schema, vous aurez :

DROP TABLE essai;
CREATE TABLE essai
 (
	CategorieID  Long Integer, 
	CategorieNom Text (30), 
	Description  Text (255)
);
-- CREATE ANY INDEXES ...
[...]
-- CREATE ANY Relationships ...
-- relationships are not supported for access

qui indique que les relations dans une base Access ne sont pas reconnues pour établir un schéma DDL pour les autres bases, ce qui peut être génant dans certains cas. Il faut donc les rétablir dans la base de données résultante.

Installation

Il est toujours possible de les compiler, mais des paquets sont aussi disponibles pour les diverses déclinaisons de Linux et pour Mac OS X avec MacPorts, Fink ou Homebrew. C'est cette dernière solution, beaucoup plus légère, que j'ai adoptée.

Par exemple sur Ubuntu :

sudo apt-get install mdbtools

ou sur Mac OS X avec Homebrew :

sudo brew install mdbtools

Principes

La démarche utilisée pour convertir une base Microsoft Access peut être résumée de la manière suivante :

• génération d'un fichier sql  pour créer le schéma avec la commande mdb-schema. Le type de la base résultante peut être spécifié :
  • mdb-schema mon.mdb >create.sql -> générique
  • mdb-schema mon.mdb  oracle >create.sql -> Oracle
  • mdb-schema mon.mdb  postgresql  > create.sql -> PostgreSQL
  • etc.
• lister les tables présentes avec la commande mdb-tables :
  
  • mdb-tables mon.mdb
  • mdb-tables -1 mon mdb (le -1 permet d'avoir une seule table par ligne)
• exportation de chaque table en CSV ou en SQL (INSERT)  avec la commande mdb-export :
  
  • mdb-export mon.mdb matable >export.cvs  -> fichier CSV
  • mdb_tables -I mon.mdb matable >export.sql -> fichier SQL insert
• importation de create.sql puis de export.sql dans la base résultante ;
• éventuellement utilisation de mdb-sql pour ne sélectionner que certaines parties des tables.

Ces commandes peuvent être regroupées dans des scripts Bash plus ou moins simples comme par exemple vasundhar.blogspot.com/2011/04/mdbtools-to-import-to-mysql-or-any.html, pnenp.wordpress.com/2011/02/10/converting-ms-access-mdb-files-to-sqlite-mdb2sqlite/, user.services.openoffice.org/en/forum/viewtopic.php.

Les commandes SQL peuvent contenir des erreurs qui doivent être corrigées avant insertion. Ainsi, le type SQLite n'existe pas d'origine et des corrections peuvent être nécessaires.  Un compromis acceptable est le type sybase qui ne nécessite aucune correction : 

mdb-schema mon.mdb  sybase  > create.sql

En pratique

A titre d'exemple, la base de référence Northwind.mdb sera convertie en base SQLite

# exportation du schéma 
$ mdb-schema northwind.mdb  sybase >create.sql
 
#importation dans la base SQLite, création des tables etc.
$ sqlite3 nw.db3<create.sql
 
# ou directement
$ mdb-schema $mdb sybase | sqlite3 nw.db3
 
# lister les tables dans northwin.mdb
$ for t in `mdb-tables northwind.mdb` ; do
> echo $t
> done
NW_Categories
NW_Customers
[...]
 
# importation des valeurs dans SQLite, table par table (-I, insert SQL, -R, ; comme séparateur)
$ for t in `mdb-tables northwind.mdb` ; do
> mdb-export -I -R ";\n" northwind.mdb $t |sqlite3 nw.db3
> done
 
# ou 
$ mdb-tables -1 northwind.mdb | xargs -n1 mdb-export -H -I northwind.mdb >export.sql
# le fichier export.sql doit être vérifié préalablement
$ sqlite3 nw.db3<export.sql

Le résultat est une base SQLite où toutes les tables de la base Northwind.mdb sont présentes, mais pas les relations entre tables établies dans Microsoft Access  (n'oubliez pas le  « relationships are not supported for access »). De même, des types de colonnes spécifiques comme OLE ne seront pas reconnus (comme Picture de type OLE dans la table NW_Categories, qui restera vide, par exemple) mais si l'on veut vraiment, cltb.ojuba.org/en/articles/mdb2sqlite.html montre comment les adapter en transformant les champs Memo, Hyperlink, Date en Text, et OLE en Blob à l'aide d'expressions régulières.

base SQLite résultante  visualisée avec SQLite Manager

Pour les Sigistes,  SpatiaLite à la rescousse

Non, non, ne rêvez pas, il vous sera possible d'extraire les tables d'une « ESRI Personal GeoDatabase » mais de là à la reconstituer dans un SIG...  Il est alors plus profitable d'utiliser GDAL dont les développeurs ont  fait le travail pour nous.

Plus intéressante est la démarche de Barry Rowlingson (geospaced.blogspot.com/2009/12/converting-mdb-to-spatialite.html) qui montre comment transformer une base Access provenant de l'Agence Européenne pour l'Environnement ( www.eea.europa.eu/) en base SpatiaLite. Il s'agit de la base du Registre européen des émissions de polluants (EPER)  disponible à www.eea.europa.eu/data-and-maps/data/eper-the-european-pollutant-emission-register-3 au format Microsoft Access (eper_dataset_27-03-2008.zip)

L'examen de la base montre qu'au moins une table avec des colonnes longitude, latitude et Système de coordonnées (WGS 84) existe, la table Facility :

base eper_dataset_27-03-2008.mdb visualisée avec MDB Viewer (Mac OS X)

Barry Rowlingson la transforme en une base SQLite/SpatiaLite :

$ mdb=EPER_dataset_27-03-2008.mdb
 
$ # création de la base SQLite/SpatiaLite
$ mdb-schema $mdb sybase | spatialite eper.db3
$ for t in `mdb-tables $mdb` ; do
> mdb-export -I -R ";\n" $mdb $t |spatialite eper.db3
> done

Résultat

base SQLite résultante  visualisée avec SQLite Database Browser

Pour que la table Facility devienne une véritable table spatiale SpatiaLite, il est nécessaire de lui adjoindre une colonne de type Geometry :

SELECT AddGeometryColumn('Facility', 'the_geom', 4326, 'POINT', 2);

puis remplir la colonne avec les points en longitude-latitude dans le système de projection EPSG 4326 (WGS 84) :

UPDATE Facility SET the_geom = MakePoint(Longitude,Latitude,4326)

Le résultat est une base SpatiaLite qui peut être visualisée dans Quantum Gis :

base SQLite/SpatiaLite  résultante  visualisée avec Quantum GIS

et traitée avec des extensions comme Qspatialite (jonctions avec les tables non spatiales, etc.) de  Romain Riviere présentée sur le ForumSIG (www.forumsig.org/showthread.php) :

base SQLite/SpatiaLite  résultante  dans Qspatialite : seule la table Facility est bien une table spatiale.

Conclusions

Et voilà, avec un peu de curiosité, cela ne servait à rien de râler. Bon, c'est vrai, il faut jouer avec la ligne de commande ce qui peut être gênant pour ceux qui sont habitués aux belles interfaces graphiques, mais c'est mieux que rien.

Une autre solution est fournie par mdb-sqlite, basé sur la librairie Java Jackcess (qui permet de lire et d'écrire les bases Microsoft Access 2000/2007),  et dont le code est à priori plus simple.mdb-sqlite ne permet lui, que de traiter les bases Microsoft Access 2000.

java -jar dist/mdb-sqlite.jar eper_dataset_27-03-2008.mdb test.db3

mais avec moins de possibilités de traitement.

Les impatients diront sans doute, et les bases Microsoft Access 2007 (.accdb ) ? Il ne faut jamais désespérer...

Tous les traitements ont été effectués sur Mac OS X, avec la dernière version de développement de MDB-Tools (par Homebrew), MDB Viewer (version 1.0.3) , SQLite (version 3.7.4), SpatiaLite (version 2.4.0) et mdb-sqlite (version 1.0.2).

Illustrations ©2000 Marsu by Franquin (pour l'image d'introduction)

licence Creative Commons Paternité-Pas d'Utilisation Commerciale-Partage des Conditions Initiales à l'Identique Commerciale 2.0 France