Openoffice.org with SQL Databases: Difference between revisions

From GRASS-Wiki
Jump to navigation Jump to search
(some more screenshots)
Line 104: Line 104:


=== Step by step DB import procedure (MacOSX) ===
=== Step by step DB import procedure (MacOSX) ===
Nota la mancanza di driver SDBC su piattaforma OSX, un alternativa per connettere Neoofice con PostgreSQL è possibile utilizzando driver JDBC.
<br>
1) Scaricare i driver direttamente del sito posgresql (scegliere il driver corrispondente alla versione di posgree che si utilizza)
[http://jdbc.postgresql.org/download.html PosgreeSQL]
<br>
ad esempio per postgreSQL-8.1 download del driver:
JDBC 8.1-407 JDBC 2
2) Aprire Neooffice
- preferenze:
http://img118.imageshack.us/img118/109/preferenzeap3.png
scegliere :
 
  Java
http://img236.imageshack.us/img236/6004/javaqr9.png
clik su : 
  classpath
http://img50.imageshack.us/img50/3504/immagine1tb1.png
click su :
  aggiungi archivio
selezionare il driver scaricato
click su :
  ok
3) Tornare a : neooffice - database
http://img67.imageshack.us/img67/2043/databasevn2.png
nella finestra di configurazione del nuovo database:
http://img54.imageshack.us/img54/3386/selezionadatabnv6.png
nella sezione :  collega a un database esistente, selezionare :
  JDBC
clik su :
  avanti
http://img111.imageshack.us/img111/2128/immagine3uw6.png
inserire l'url per il collegamento al database in postgresql (nel caso in esempio host=localhost, dbname=grassdb):
  postgreesql://localhost/grassdb
classe driver JDBC :
  org.postgresql.Driver
click su :
  classe di prova
se tutto è correto :
http://img125.imageshack.us/img125/9330/immagine2kw6.png
clik su :
  ok
avanti e proseguire con la registrazione del database
http://img67.imageshack.us/img67/8527/immagine13dv9.png
inserire :
  nome utente
  richiesta password
clik su :
  connessione di prova
 
http://img300.imageshack.us/img300/9965/immagine6ck5.png
inserire :
  password
click su :
  ok
se tutto è corretto :
http://img64.imageshack.us/img64/896/immagine7ak2.png
click su :
  avanti
http://img103.imageshack.us/img103/8379/immagine8kf6.png
click su
  fine
per salvare il database
http://img109.imageshack.us/img109/815/immagine9ph2.png
il database è stato configurato con successo:
http://img113.imageshack.us/img113/686/immagine10di9.png


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

Revision as of 20:31, 3 December 2006

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)

  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 (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.

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.

Step by step DB import procedure (MS-Windows and Linux)

Here we show how to get a spreadsheet table into a SQL database.

1. Mark and copy to clipboard the relevant table parts in the Excel/CSV/whatever table (OO-Calc):

Open the original table with OpenOffice-Calc. For CSV files, they need the .csv extension.

(see also larger screenshot)

2. Open OO-Base and establish a connection to the database (see above for driver installation):

Go File -> New -> Database to launch OO-Base.

(see also larger screenshot)

(see also larger screenshot)

(see also larger screenshot)

3. In the table wizard, paste (right mouse button) earlier selected OO-Calc spreadsheet into the table section:

First select "table" from left menu, then paste into it:

(see also larger screenshot)

4. A wizard opens, define a table name (when connecting to PostgreSQL, write "public." as prefix for the schema):

(see also larger screenshot)

5. Select the columns you want to transfer:

(see also larger screenshot)

6. Define primary key (right mouse button on desired column) and all column types:

(see also larger screenshot)

7. Save and enjoy. The table is now written into your SQL database.

Note that the conversion can take some time.

You can now connect the table to GRASS, R-stats, ... as you like.

Step by step DB import procedure (MacOSX)

Nota la mancanza di driver SDBC su piattaforma OSX, un alternativa per connettere Neoofice con PostgreSQL è possibile utilizzando driver JDBC.
1) Scaricare i driver direttamente del sito posgresql (scegliere il driver corrispondente alla versione di posgree che si utilizza) PosgreeSQL
ad esempio per postgreSQL-8.1 download del driver:

JDBC 8.1-407 JDBC 2


2) Aprire Neooffice - preferenze:

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

scegliere :

  Java

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

clik su :

  classpath 

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

click su :

  aggiungi archivio 

selezionare il driver scaricato

click su :

  ok

3) Tornare a : neooffice - database

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

nella finestra di configurazione del nuovo database:

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

nella sezione : collega a un database esistente, selezionare :

  JDBC 


clik su :

 avanti 

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

inserire l'url per il collegamento al database in postgresql (nel caso in esempio host=localhost, dbname=grassdb):

  postgreesql://localhost/grassdb

classe driver JDBC :

 org.postgresql.Driver

click su :

 classe di prova

se tutto è correto :

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

clik su :

  ok

avanti e proseguire con la registrazione del database

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

inserire :

  nome utente
  richiesta password

clik su :

  connessione di prova


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


inserire :

 password

click su :

  ok

se tutto è corretto :

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

click su :

  avanti

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

click su

 fine

per salvare il database

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

il database è stato configurato con successo:

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