Importing data into PostgreSQL using Open Office Base 3.2   

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.
   
3. Then you paste it into your Open Base window (preferably in Table section) that is connected to your PostgreSQL database.
   
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.
   
   
5. Then the wizard guides you thru selecting the 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.
   

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.

• Open Office Base maintains the casing of the headers, so you will want to rename these to lower case either before import or during import. If you use the SDBC driver, it won't let you rename to lower case (it gives duplicate name error) if you simply try to lower case the column names, but JDBC works fine.
• By default for the JDBC driver, the data type of all the fields is set to TEXT, unless you change them which is okay for the most part, but for the SDBC driver it sets it to TEXT (char) which magically gets mapped to some non-existent data type that when it goes to create the table, fails horribly. To get around this annoyance, you have to change the data type of every single column to Text(Text) or some or TEXT (varchar) with the wizard before you import.
• Make sure to prefix new tables with the schema. we tried pasting using an account that had limited rights and it tried to create a table under a schema that didn't exist (presumably the users). which resulted in a create not allowed error.

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

• Copy a table and create a new table from it simply by pasting.
• Copy a table and append the contents to an existing table. In order to do this, first copy the table, then select the table you want to paste into, and then do a Ctrl-V to paste (or choose Edit->Paste from file menu)
• Copy a table and make it a view. Not quite sure why this is that useful since you can't edit the definition of the view in OO.