Importing data into PostgreSQL using Open Office Base 3.2

A while ago we demonstrated how to use Open Office Base to connect to a PostgreSQL server using both the native PostgreSQL SBC and the PostgreSQL JDBC driver.

The routine for doing the same in Open Office Base 3.2 is pretty much the same as it was in the 2.3 incarnation. In this excerpt, we'll demonstrate how to import data into PostgreSQL using Open Office Base, as we had promised to do in Database Administration, Reporting, and Light Applicaton Development and some stumbling blocks to watch out for.

Use Case

Command line lovers are probably scratching there head, why you want to do this. After all stumbling your way thru a commandline and typing stuff is much more fun and you can automate it after you are done. For our needs, we get stupid excel or some other kind of tab delimeted data from somebody, and we just want to cut and paste that data in our database. These files are usually small (under 5000 records) and the column names are never consistent. We don't want to fiddle with writing code to do these one off type exercises.

For other people, who are used to using GUIs or training people afraid of command lines, the use cases are painfully obvious, so we won't bore you.

Importing Data with Open Office Base Using copy and paste

Open Office has this fantastic feature called Copy and Paste (no kidding), and we will demonstrate in a bit, why their copy and paste is better than Microsoft Access's Copy and Paste particularly when you want to paste into some database other than a Microsoft one. It is worthy of a metal if I dear say.

How does this nifty copy and paste feature work?

For this little exercise, we downloaded the file Export-Import Bank Authorizations for FY 2010

  1. You open up a delimited or spreadhseet file of some sort either in Excel or OpenOffice Calc.
  2. You click on the top left corner (or select the cells you want to copy). and use Ctrl+c or Edit->copy from menu.
    open Office Copy
  3. Then you paste it into your Open Base window (preferably in Table section) that is connected to your PostgreSQL database.
    open Office Table view section
  4. A wizard comes up asking you if you want to create a new table or append to an existing table. If you want to create a new table, give the table a name, preferably include the schema in the name. If you would have liked to append to a table, make sure you have the table selected before you start this whole copy paste routine.

    open Office Paste
  5. Then the wizard guides you thru selecting the columns.
    Mapping columns
  6. To which you have the option of selecting all with the >> or just some by holding the control key down, selecting the columns and then click >
  7. If you want to append to an existing table you get to map the fields. If a new you get to change the data types and names of the fields. Make sure to make them all lower case and no spaces to save yourself future grief.
    Renaming columns

Caveats

The SDBC driver is much more annoying than the JDBC driver for these exercises and we'll explain why. So short answer, you are probably better off using the latest PostgreSQL JDBC 4 driver.

In addition to all the new features in OpenOffice, we have a nice new shiny logo on the flash screen to remind us of who is boss. All hail to our new warlord and may the Oracle treat us well.

Other things you can do with Copy and Paste