Getting data into and out of an OpenOffice.org Base database

The ability to import and export data is crucial to any database management system, and OpenOffice.org Base is no exception. While you may be surprised to discover that OpenOffice.org lacks a dedicated import/export feature, it does allow you to get data into and out of a database in a variety of ways.

Let’s start with the most simple scenario, where you have data in a Calc spreadsheet and you want to import it into a new table in a Base database. Before you begin, make sure that the first row in the spreadsheet contains column labels. They will be used as database field names, thus making it easier to manage data during the import process. Copy all the data in the spreadsheet using the Ctrl-A and Ctrl-C keyboard shortcuts (or choose Edit -> Select All and Edit -> Copy). Switch to the database, go to the Tables section, right-click somewhere in the Tables pane, and choose Paste. This opens a wizard that guides you through the import process. Since you’re importing data into a new table, give the table a descriptive name, and choose the Definition and data option. Next, you need to create a primary key for the new table. To do this, tick the Create primary key check box and give the field a name if you don’t want to use the default ID name. Press Next to go to the Apply Columns screen. Here you can select the columns that you want to add to your table. Use the available buttons to move the desired columns, and press Next when you are satisfied with the result. The import wizard usually does a good job of guessing the type of data in the individual columns, but if you need to tweak the defaults, you can do so in the Type formatting screen. Here you can also modify the column names and change the length of each field. Finally, press the Done button to import the data into the table.

Importing data into an existing table is equally straightforward. Select and copy the data in the spreadsheet, right-click somewhere in the Tables pane, and choose Paste. Enter the name of the table you want to use in the Table name field. Keep in mind that the name you enter must match the name of the existing database; otherwise Base will create a new table instead of using the existing one. Select the Append data option and leave the Create primary key check box untouched. Press the Next button, and use the arrow buttons in the Assign columns screen to align fields in the source and destination. Press Create to insert the data into the table.

Using Calc as an intermediary tool, you can process data from an HTML table in a Web page, then import it into a Base database. Let’s say you want to create a database table containing a list of irregular English verbs and their forms using an existing HTML table. Select and copy the data in the table, create a blank spreadsheet, and choose Edit -> Paste. Calc is smart enough to figure out that the data in the clipboard is an HTML table and insert it into separate columns. If Calc has trouble populating the columns correctly, use the Edit -> Paste Special command and select the Unformatted text option. This opens the Text Import dialog, which gives you a few tools that can help you to import the data properly. Once you have the data in the spreadsheet, you can import it into the database as described above.

Exporting

OpenOffice.org Base also offers a few different options when it comes to getting data out of a database: you can insert a single or all records into a Writer document, or export the data into a Calc spreadsheet either manually or via an OpenOffice.org Basic macro.

Inserting records into a Writer document is straightforward, as OpenOffice.org has a dedicated Data to Text feature for that. To make use of this feature, you have to register the database as a data source, so it can be accessed from any OpenOffice.org application. To do this, choose Tools -> Options -> OpenOffice.org Base -> Databases and press the New button. Select the database, give it a name, and press OK. Now you can access tables in the database via the Data Sources pane, which you can evoke by pressing F4 or choosing View -> Data Sources. Place the cursor in the Writer document where you want to insert the data, select the desired records in the table pane, and press the Data to Text button. This opens the Insert Database Columns dialog window, which offers three ways to insert the selected records into the document: as table, as text, and as fields. To insert the records as a formatted table, select the Table option in the “Insert data as” section, and move the columns you want to the Table Columns window. You can use the Properties button to specify the table settings, or you can use the AutoFormat button to choose a predefined table layout. Press the OK button to insert the table into the document. If you want to insert the selected records as text, select the Text option and specify the fields you want to use. You can apply paragraph formatting to the inserted data by selecting a style you like from the Paragraph Style drop-down list. Finally, the Fields option allows you to insert records as placeholders that can be updated dynamically. For example, if you insert an address as a set of fields, and then make changes to the original record in the database, you can update the inserted data by selecting the modified record in the database table and pressing the Data to Fields button.

Moving the data from a database table into a Calc spreadsheet is as easy as it gets. In the Tables section of the database, right-click on the desired table and choose Copy. Switch to the spreadsheet and choose Edit -> Paste. That’s it.

You can also create a simple OpenOffice.org Basic macro that will move the data for you. This can be a real timesaver if you use database data in Calc on a regular basis. The following macro connects to a database called TasksDB and uses an SQL query to retrieve the contents of the Task, Date, and Done fields in the “tasks” table. It then creates a new Calc spreadsheet and inserts the retrieved data into it.

Sub TasksToCalc()
Dim RowSetObj As Object, ConnectToDatabase As Object

DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource=DBContext.getByName("TasksDB")
ConnectToDatabase=DataSource.GetConnection ("","")

oURL="private:factory/scalc"
oDoc=StarDesktop.loadComponentFromURL(oURL, "_blank", 0, Array())
oSheet=oDoc.Sheets(0)
oSheet.Name="Tasks"

SQLQuery= "SELECT ""Task"", ""Date"", ""Done"" FROM ""tasks"" ORDER BY ""Date"" ASC"
SQLStatement=ConnectToDatabase.createStatement
RowSetObj=SQLStatement.executeQuery (SQLQuery)

While RowSetObj.Next
i=i+1
oCell=oSheet.getCellByPosition(0,i)
oCell.String=RowSetObj.getString(1)
oCell=oSheet.getCellByPosition(1,i)
oCell.String=RowSetObj.getString(2)
oCell=oSheet.getCellByPosition(2,i)
oCell.String=RowSetObj.getString(3)
Wend

Database.close
Database.dispose()

End Sub