Importing Microsoft Access MDB into @PostgreSQL on #Linux #postgres

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:

mdb-tables manufacturer.mdb

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

 

2 thoughts on “Importing Microsoft Access MDB into @PostgreSQL on #Linux #postgres

  1. for TT in $(mdb-tables file.mdb); do
    mdb-export -D ‘%%Y-%%m-%%d %%H:%%M:%%S’ -I -q \’ -R \; file “$TT” | psql -d db_test -U user -W -h localhost
    done

Leave a Reply