Creating Databases the Quick, Easy Way With OpenOffice.org

Contributor Icon Contributed by solveig Date Icon February 25, 2007  
Tag Icon Tagged: OpenOffice

Create a database with two quick approaches using Base, the database tool in OpenOffice.org, the free Microsoft Office compatible office suite. This is the first in a series of articles that describe how to create a database; then how to create mail merges.

Do you need to make a database, but fear it’s too much of a pain or you don’t have the right tools? Don’t worry: it’s easy, free, and useful, too. Use the free OpenOffice.org office suite to get your data in shape for mail merges, queries, or useful analysis of your business data.

What’s the Point of Making a Database?

Everyone doesn’t need a database. But sooner or later, anyone who has to keep track of and retrieve important in$pformation is going to benefit from one. Databases are for storing data, of course, but their benefit comes in letting you get at the data you want, and displaying or printing it the way you want. For instance, once you have a database, you can of course do mail merges, whether it’s a letter to each of the 500 people you met at the open source convention or printing a label for each of your 870 legal DVD backups. Databases are also necessary for setting up data entry or data viewing forms; creating relations between tables so you can print connected data; or for creating a list listing the average number of items your customers bought per purchase during the holiday shopping season.

What Kind of Database Do You Create?

Once you’ve decided to create a database, the way ahead looks simple. You just choose:

File → New → Database

However, then one last question remains: what type of database do you create? Do you connect straight to your Oracle database? (This is best only if you are or have access to an Oracle guru.) Do you investigate some of the simpler choices such as spreadsheets, or if you are well informed or psychic, do you choose the entirely invisible option of importing existing data?

If you’re a database god and enjoy spending time deep in a database—well, you can do anything you want. If you want the most results for the least trouble, here’s what I recommend.

Creating a Simple Database That Connects to a Spreadsheet

The simplest approach is to simple create an OpenOffice.org database file that points to a spreadsheet chock full of data. The spreadsheet doesn’t have the power to do mail merges and the other database-related features I mentioned earlier, but the database file pointing to it can. It acts as a middle man saying “The data’s over there, with these fields – go get it.”

You either have your data in a spreadsheet already, or you can get it into a spreadsheet pretty easily. You’ll want to have it set up to look something like this, with field labels across the top, and with each piece of data separate.

Figure 1: Spreadsheet data


Now you’re ready to create the database. It’s pretty simple.

1. Choose File → New → Database.

2. In the first window, select Existing Data Source, and Spreadsheet type. Click Next.

Figure 2: Choosing to create a spreadsheet-based database


3. In the next window, point to the full path (I’ve just shown the filename here) of the spreadsheet you want to use. Click Next.

Figure 3: Specifying the spreadsheet name and path


4. Leave all the checkboxes marked. You don’t need to edit the database but the first time, at least, it’s good to take a look at what the main database window looks like. Click Finish.

Figure 4: Finishing the database


5. Name the database. This name will show up when you do mail merges, and it’s the name and location you’ll look for when you want to do things with the database like create queries or reports. Click Save.

Figure 5: Naming and saving the database


6. You’ll see the database. Click the Tables icon at the left, and select a table name. Each sheet containing data in your spreadsheet will become a table. Now, on the right side, instead of None, select Document. You’ll see the data in the spreadsheet.

Figure 6: Viewing the database file that connects to your spreadsheet


There’s nothing else you have to do. Here’s a little more information, though.

Updating the Data

When you have more data, just add it to the spreadsheet. When you want more tables, just add the data to another sheet in the spreadsheet.

What You Can’t Do With This Type of Database

There are some fairly cool features that you might or might not need. You can’t, for instance, use the functions that can tell you for every invoice, what was the average total amount on the invoice. You can’t create data entry forms. You can’t join two tables to relate the data. But you can create queries, and reports, and data view forms. (Click the Forms icon on the left side in your database editing window, and use the wizard to create the form.)

Figure 7: A data-viewing form


Creating a Native OpenOffice.org Base Database

If you want all the power that Base can provide, and you don’t have Access or Oracle or another engine sitting out there to connect to, then you’ll want to create a native database. This is similar to start off with, but with a lot of extra steps to define the tables, and a lot of extra steps to bring the data in.

Creating the Database

1. Choose File → New → Database.

2. In the first window, choose to create a new database. Click Next.

Figure 8: Creating a new database


3. Leave the checkboxes as is and click Finish.

Figure 9: Accepting the default values


4. Just name the database and click Save.

Figure 10: Saving and naming the database


5. The database appears.

Figure 11: Your native Base database (without tables)


Creating Tables and Entering Data by Pasting

There is a long, traditional way to create tables. You can do it in design view, or using the wizard.

Figure 12: Creating a table from scratch


But this article is about telling you how to get the most bang for your effort buck. So I’m skipping that and going straight to the easy, invisible way. This way is an advantage only if you have the data out there somewhere: in another database, in some CSV file, in an Excel or Openoffice.org Calc spreadsheet, or even in a big HTML table.. But that’s the case most of the time.

1. Get the data into a spreadsheet. You can export the data from your database to spreadsheet or CSV, or copy it from an HTML table if it’s not there already.

  • From HTML: Copy the table, then click in one cell of a spreadsheet and paste.
  • From CSV (comma-separated files): In OpenOffice.org choose File → Open.

    • * In the File Type list of the Open window, select Text CSV (click in that list and type T four times).

    Figure 13: Specifying Text CSV format so that the file will open in a spreadsheet


    * Select the CSV file and click Open. In the window that appears, verify that the settings are correct for the data, then click OK.

    Figure 14: Checking the conversion settings for the text file


  • From Excel: Just open the spreadsheet in OpenOffice.org.

2. Make sure the data is set up right. Make sure there are field headings, that the data is granular enough, etc. If you don’t have a unique primary key field such as an ID, don’t worry; you can create one on the fly as you add the data.

3. In the spreadsheet, copy all the data including the headings.

4. Go to the database file and click the Tables icon at the left side.

5. In the Tables area, right-click and choose Paste.

Figure 15: Pasting the data to create a table


6. In the window that appears, type the table name you want, and choose to add both the data and the definition. You can also choose to create a new field that will function as the primary key. Click Next.

Figure 16: Specifying what should be copied and created in the new table


7. Add all the fields that you want in the table, then click Next.

Figure 17: Specifying the fields you want


8. Make any changes to the field definitions. Click Create.

Figure 18: Modifying field definitions


9. The table will appear in the database. Select the table, and from the right side choose Document rather than None, and you’ll see the data. Close the database, saving changes.

Figure 19: Viewing and saving the table and database


You’re done—you’ve got a fully functioning native Base database with a table. You can create views, relations, data entry forms, reports, write SQL queries on the data, and anything else that Base provides.

Adding More Tables

Just repeat the pasting process with another set of data.

Changing the Table Definition

Right-click on the table name and choose Edit.

Figure 20: Choosing to edit the table definition


You’ll then see design view, where you can change some aspects of the definition. For instance, to make a field auto-value, just make it an Integer type, then choose Yes under AutoValue at the bottom of the work area.

Figure 21: Modifying the table definitions


If you want to change the primary key, right-click on the field to make the primary key and select Set Primary Key.

Figure 22: Modifying the table definitions


Updating the Data

To add data, paste the new data again the same way you did before – select the data and choose Paste. In the window that appears, just choose to Append, and make the same choices you did last time.

Figure 23: Appending data


Alternately, double-click the table name and change or add the data just by typing.

Another approach, if you’re going to need to do a lot of updating, is to create a data entry form. Click the Forms icon on the left side of the database editing area and use the forms wizard to create the form. Be sure to state, when prompted, that the form is for both viewing and entering data.

About the Author:Solveig Haugland has worked as an instructor, course developer, author and technical writer in the high-tech industry for 16 years. She is the author of the OpenOffice.org 2 Guidebook and blogs about OpenOffice.org at openoffice.blogs.com. To inquire about OpenOffice.org training and consulting, see her business site or email training@getopenoffice.org.

Previous recipe | Next recipe |
 

 
close Reblog this comment
Powered by Disqus · Learn more
blog comments powered by Disqus