Openoffice.org with SQL Databases: Difference between revisions

From GRASS-Wiki
Jump to navigation Jump to search
(Category: FAQ)
(primary key)
Line 51: Line 51:
== Converting Excel, CSV, ... to PostgreSQL/MySQL/... via OO-Base ==
== Converting Excel, CSV, ... to PostgreSQL/MySQL/... via OO-Base ==


* [http://searchopensource.techtarget.com/tip/0,289483,sid39_gci1222186,00.html 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. '''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.
* [http://searchopensource.techtarget.com/tip/0,289483,sid39_gci1222186,00.html 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.


[[Category: FAQ]]
[[Category: FAQ]]

Revision as of 14:13, 21 November 2006

Usage 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

  1. download postgresql-sdbc-X.Y.Z.zip driver (take the "recommended" file) and save the file to your disk.
  2. 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.
  3. Restart openoffice (even the quickstarter on windows must be shut down !!!).
  4. Done (did you really close openoffice? Please do.)

Usage of PostgreSQL driver

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.

MySQL (OpenOffice.org 2.x)


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)

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


SQLite files (OpenOffice.org 2.x)

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.