Tip of the day: getting Excel/Access into PostgreSQL

Do you also dislike it to manually convert Excel tables into PostgreSQL? Not having real fun in exporting to CSV, then manually writing the SQL header and importing the stuff in PG? After messing around with tools like csv2sql.pl, xbase2pg and other stuff, I found this great article today:

Bringing data into OpenOffice 2.0’s database by Solveig Haugland

It explains how to get data out of one database, such as Access, into an OpenOffice database table. Using for example the PostgreSQL driver, the data will then be directly stored there. Essentially you open the Excel (or whatever) table with OpenOffice-Calc, select and copy the full table or a subset to the clipboard, and simply paste it into OpenOffice-Base with the right mouse button. A wizard pops up which let’s you define the type of each column and a primary key. Save & done. Can’t be easier!

Find a more detailed page on how to connect OpenOffice-Base to various DBMS here.

This entry was posted in SQL on by .

About Markus

Markus Neteler , a founding member of FOSSGIS.de (D-A-CH), GFOSS.it (Italy) and the Open Source Geospatial Foundation (OSGeo), was head of the GIS and Remote Sensing Unit at the Research and Innovation Centre of the Fondazione Edmund Mach, Trento, Italy from 2008 to 2016. In 2015 he co-founded the company mundialis (Bonn, Germany), a start-up specialised in open source development and massive data processing. He is the author of several books and chapters on GRASS and various papers on GIS applications. Passionate about open source GIS, he became a GRASS GIS user in 1993 and a developer in 1997, and has been coordinating its development ever since.

3 thoughts on “Tip of the day: getting Excel/Access into PostgreSQL

  1. markusN

    Extra hint: If generating a PostgreSQL table, prefix the new table name with “public.” (example: public.table1) to avoid that the user name is used as schema name.

Comments are closed.