This is a read-only archive. Find the latest Linux articles, documentation, and answers at the new Linux.com!

Linux.com

Feature: Office Software

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

By Dmitri Popov on April 23, 2008 (7:00:00 PM)

Share    Print    Comments   

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
Dmitri Popov is a freelance writer whose articles have appeared in Russian, British, US, German, and Danish computer magazines.

Share    Print    Comments   

Comments

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

Note: Comments are owned by the poster. We are not responsible for their content.

What is OpenOffice Base all about?

Posted by: hopethishelps on April 24, 2008 02:34 PM
I'm confused about what exactly OpenOffice Base is for.

There are already good, mature free-software databases: MySQL, PostgreSQL. Why do we need another one that isn't as good?

Is OO Base intended as a front-end to a database? If so, why doesn't it focus on that and rely on a MySQL or PostgreSQL backend?

Did the OO.o people decide they needed a half-assed database because Microsoft Office has one? (Access ... actually I'm being kind to Access by calling it a half-assed database, it isn't really a database at all .... but I digress).

I'd like to see an article that explains that to me.

#

Re: What is OpenOffice Base all about?

Posted by: Jestar on April 24, 2008 08:43 PM
While the failings of MS Access are many, it is still a light weight desktop database. OOo Base was added to support the need for a light weight desktop database integrated into the OOo suite. OOo does contain front-end hooks to full SQL databases through ODBC, JDBC, etc. but the need for something simpler for storing names and addresses to be able to do a mail merge had been a major failing of OOo for quite some time. Base was the answer. Simple, light weight, and reliable. It does the stuff you would do with MS Access and a bit more, but doesn't take the place of a more serious database such as MySQL or Postgre. It is NOT a half-assed database and still needs some refinements, just a simple solution to light weight database needs - think SQLite here.

Hope that helps.

#

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

Posted by: Anonymous [ip: 75.163.128.174] on April 25, 2008 01:29 AM
See also Solveig Haugland's great articles on getting data in and out of OpenOffice.org Base
http://openoffice.blogs.com/openoffice/2006/12/techtarget_arti.html

Andrew

#

Re: What is OpenOffice Base all about?

Posted by: Anonymous [ip: 195.84.167.2] on April 25, 2008 10:41 AM
1. It's a database front end with enormous connectivity. You may browse data, create queries, forms and reports. MS Access lacks the connectivity, but is stronger on forms and reports.

2. It's completely integrated with the other Open Office tools. MS Office, despite marketing hype, is a set of independent tools that slowly have been coerced into a family. The seams and different personalities are quite visible.

3. Why would anyone compare OOo Base to MySQL or PostgreSQL? I agree it's the OOo component that is hardest to understand because there is no real counterpart in MS Office. Some people need to realise that OOo is not a copy of something else, but an original creation with interfaces that allow it to interact with legacy technology.

Hakan

#

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

Posted by: Anonymous [ip: 71.243.74.141] on April 25, 2008 07:18 PM
I've worked with OO.o Base for about 3 weeks (in parallel as I studied Microsoft's db.) No comparison. On a scale of one to ten I would give it a 2. Sun and company added it as an afterthought and because the default backend database was Java driven. You can't do simple things like reorder data fields. After creating them left to right they are locked in place, etc... I could see someone recommending MySQL or PostgreSQL. You can add a front-end (phpmyadmin or pgadmin3) find more documentation and get more done. Being able to connect to multiple databases using the OO.o base front-end but only having a subset of features operational w/each database is no reason to use it.

#

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

Posted by: Anonymous [ip: 139.142.50.184] on April 25, 2008 09:11 PM
I cannot comment on the comparison between MS Access vs Ooo Base as it applies to forms or reports. What I would like to comment on is its ability to connect to a variety of databases, giving me access to the data therein. I develop websites and web applications that heavily rely on a database backend. I use Ooo Base extensively to look at my data. I find it more useful than PHPMyAdmin because of the ease with which I can order data based on a particular field (or multiple fields, with a few extra mouse clicks) and because of the simple ways of getting data out to a word processor for printing (useful when examining sets of data). I can also create and save SQL queries that will again allow me to export to a word processor for printing.

Just my views and comments on a valuable tool I use regularly,
Rodney

#

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

Posted by: Anonymous [ip: 86.14.236.140] on April 27, 2008 08:16 AM
I recently been implementing a database frontend for a MySQL database in both MSAccess and OO-Base. I can say with confidence, Access is terrible in terms of it's integration with external non-MS database servers (I never tried with MS-SQL). This is typical Mmicrosoft: won't integrate with anything other than MS product, to maximise their Vendor lockin. By contrast, Base is a joy as a frontend to either MySQL or Postgres and it's getting better as the OOo-native drivers for these databases mature (this removes the need for ODBC or JDBC). Base's Forms functionality doesn't have as wider range of control as Access but it has all the basics and it's very easy to use. You can design your tables (Access won't allow this) including relationships and you can execute SQL directly where necessary (if you can do this in Access I never found it; a UI failure if nothing else). Following Sun's purchase of MySQL I think we can expect integration with Base to improve further.

Base is still maturing, it clear. The "user management" feature of Base is not implemented yet; it's be great when it's working (multi-user support in Access is also absent as far as I can see - it's uses lock-files). Report generation is Base's biggest weakness and, I'm afraid, the Sun Report Builder doesn't improve this much. Laying out reports with this is a PITA. Still, there are many other better reporting tools like Eclipse BIRT which do a proper job and can connect directly to the server, or even directly to the HSQLDB (it's all java).

#

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

Posted by: Anonymous [ip: 68.202.49.183] on May 12, 2008 11:10 PM
I'm a casual user, just looking to import a word document into the Open Office spreadsheet. The columns and the info are comma delineated and I just want a simple list, no manipulation. What I really need is a "do this, then do this" explanation. Can I get one? Thanks. Paul

#

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

Posted by: Anonymous [ip: 72.173.59.201] on May 14, 2008 06:36 AM
I am not a programmer, I understand data base structure as far as organizing data in and data out. I have learned by trial and error over 15 years of making my own data bases using MS access. I have used MS Access to write up simple databases for running warehouses. My current job uses a database I created over ten years ago in Access and have modified it bit by bit over the years. I agree that connectivity is a problem with Access, but if the data is all self contained within MS programs which is common in small businesses, it is a very easy way for a non programmer to make a sophisticated front end for a single or a few user business to enter and retrieve the data it deems important.
I am very new to Base, so far I am not impressed. There needs to be more effort into menu driven commands to create querys, macros, forms, etc. I want to see this Open Office Base program succeed but it has to be as easy to use for us non programmers as Access has become. That is the strength of access, the ability for a non programmer to create a decent database in a business application. I do not know all I should about Base yet and may be jumping the gun. I found that creating the tables was intuitive but once I got to the queries and forms I started losing my ability to find commands to do what I wanted to do and the macro's lost me completely. Is there a manual written on using Base? I will keep trying. Even simple extras like a group of graphics for buttons created on your forms would help make the finished form more user friendly.
Kirby

#

This story has been archived. Comments can no longer be posted.



 
Tableless layout Validate XHTML 1.0 Strict Validate CSS Powered by Xaraya