To load a Microsoft Access MDB file into a PostgreSQL database there are two steps necessary which are presented in this article.
The first step is to export the MDB tables to stdout using mdbtools and then loading the data into PostgreSQL using psql from stdin.
In order to use mdbtools, you need to install the application:
sudo apt-get install mdbtools
Preparing the PostgreSQL Target
Once mdbtools is installed, execute the following command to retrieve the names of all tables in the MDB file:
Execute the mdb-schema command to create the target tables, either for all tables in the MDB file:
mdb-schema manufacturer.mdb postgres | psql -d olschimke -U olschimke -W -h localhost
where -d is the database, -U is the user -W forces psql to ask for a password and -h is the host name of the PostgreSQL instance.
To create only a specific table, use the following command:
mdb-schema -T WMI manufacturer.mdb postgres | psql -d olschimke -U olschimke -W -h localhost
Loading the Data into PostgreSQL
To directly loading the data from one table (WMI) into PostgreSQL, use the following command:
mdb-export -I postgres -q \' manufacturer.mdb WMI | psql -d olschimke -U olschimke -W -h localhost
Extracting the Data to CSV
Using the following command, you can extract one table (in this case WMI) to an external CSV file:
mdb-export manufacturer.mdb WMI > WMI.csv