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 |
Thank you.
But the export command doesn’t work for me. I used
mdb-export -I -q ‘ -R ; access.mdb tablename
Agreed, the import option -I does not take any arguments.
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
Thanks a lot, very useful.