Openoffice.org with SQL Databases

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? This article here explains how to convert a table into an SQL database via OpenOffice. 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!

Usage of OpenOffice-Base with various drivers

See Database drivers page for overview. There are drivers for PostgreSQL, SQLite and MS-Access mdb files.

PostgreSQL (OpenOffice.org 2.x)

Installation of PostgreSQL driver (MS-Windows and Linux)

http://dba.openoffice.org/drivers/postgresql/index.html

download postgresql-sdbc-X.Y.Z.zip driver (take the "recommended" file) and save the file to your disk.
Start OpenOffice, choose 'Tools/Package Manager'. Click on the add button and select the downladed file (DO NOT EXTRACT IT !) in the upcoming File dialog. The driver gets installed now, afterwards, the file is listed in the dialog with state enabled. In case you have installed an older version of the driver, remove it now. Close the dialog.
Restart openoffice (even the quickstarter on windows must be shut down !!!).
Done (did you really close openoffice? Please do.)

Usage of PostgreSQL driver (MS-Windows and Linux)

After starting OpenOffice.org again, use: File -> New -> Database to enter the DB dialog.

Connect to existing database (select driver): postgresql
Connection settings: We have to define the server ("host") name and the name of the database, probably also the user name in case you have to connect with a different user name. To find out, ask your DB administrator or use in a terminal "psql -l " or, if the database is on a remote server, "psql -h host.mydomain.org -l". An entry may look like this:

       dbname=pgtest host=my.databaseserver.org

User name (the login name): yourname
Finish brings you to a 'Save as' dialog for the connection, saves these settings as file. This file you can use later to re-establish the connection (instead of defining all again).
Then click on 'Tables' in the next screen to see the existing PG tables in the database. Usually the 'public' scheme contains the user tables.
The 'Queries' button takes you to the query mode, it contains a graphical query designer.

Installation of PostgreSQL driver (MacOSX)

Please use the JDBC driver.

There are not mac OSX version of official SDBC driver, you can connect your postgreSQL database using the JDBC driver.

1)Download the JDBC driver at : PosgreeSQL
(select the driver for your pgsql version) for example with postgreSQL-8.1 we need to download this driver:

JDBC 8.1-407 JDBC 2

2) Open Neooffice - preferences:

http://img118.imageshack.us/img118/109/preferenzeap3.png

select :

  Java

http://img236.imageshack.us/img236/6004/javaqr9.png

clik on :

  classpath

http://img50.imageshack.us/img50/3504/immagine1tb1.png

click on :

    add archive   ( aggiungi archivio )

select the downloaded driver

click on :

ok

3) Come back to :

  neooffice - database

http://img67.imageshack.us/img67/2043/databasevn2.png

In the database configuration window:

http://img54.imageshack.us/img54/3386/selezionadatabnv6.png

at the point : connect to an existant database (collega a un database esistente), select the option :

  JDBC

clik on :

 next (avanti)

http://img111.imageshack.us/img111/2128/immagine3uw6.png

insert the url to your pgsql database (in this example we have host=localhost, dbname=grassdb):

  postgreesql://localhost/grassdb

class driver JDBC :

  org.postgresql.Driver

click on :

 try class (classe di prova)

if oll is ok :

http://img125.imageshack.us/img125/9330/immagine2kw6.png

clik on :

ok

go to the next step

http://img67.imageshack.us/img67/8527/immagine13dv9.png

insert :

 user name

 chek the flag 'request password'

clik on :

  try connection  ( connessione di prova )

http://img300.imageshack.us/img300/9965/immagine6ck5.png

insert :

 password

click on :

ok

if oll is ok :

http://img64.imageshack.us/img64/896/immagine7ak2.png

click on :

  next (avanti)

http://img103.imageshack.us/img103/8379/immagine8kf6.png

click on

  end ( fine )

to save the database

http://img109.imageshack.us/img109/815/immagine9ph2.png

database succesfooll created:

http://img113.imageshack.us/img113/686/immagine10di9.png

MySQL (OpenOffice.org 2.x)

Connection via JODBC driver: http://kienlein.com/pages/mysql-jdbc-howto-en.html

DBF files (OpenOffice.org 2.x)

No installation needed, the driver is included in the standard version of OpenOffice.org.

MS-Access mdb files (OpenOffice.org 2.x)

http://dba.openoffice.org/drivers/mdb/index.html

Note that the driver is somewhat behind the mdbtools.sf.net development.

See also ODBC based connection: http://www.openoffice.org/FAQs/ms-access/ms-access.html

SQLite files (OpenOffice.org 2.x)

http://dba.openoffice.org/drivers/sqlite/index.html

Converting Excel, CSV, ... to PostgreSQL/MySQL/... via OO-Base

Bringing data into OpenOffice 2.0's database: You can for example open an Excel file with OpenOffice, select contents, then open a DB connection in OpenOfficeBase and paste the table into it. The wizard allows to define the type of each column before creating the table. Using the right mouse button on a column name, you can define it as primary key. Important: 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.