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.
Follow
Thanks for the tip Markus, I haven’t used OpenOffice database much before but this is a good reason to become more familiar.Tyler
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.
I have added a graphical guide onto the GRASS Wiki (screenshots of the procedure) at https://web.archive.org/web/20080324050309/http://grass.gdf-hannover.de/wiki/Openoffice.org_with_SQL_Databases