Let's say you built a house and forgot the door.
Aside from the mystery of how the builders could have gotten in and out, that house would be pretty useless, wouldn't it? The same is true of database programs like OpenOffice.org Base. If you can't get existing data into the database, it's no good. Luckily, you can, and it is.
I've written previously about how to create a database from scratch and type the data into it. This isn't the worst approach in the world if you have no particular data to start with. But if you've got a few tables with 100,000 rows in each, typing is not an option.
Getting data into Base isn't obvious, but it is easy. There are three steps:
- Get the data into a form that Base will accept
- Create a database where you want the data
- Bring the data into Base
Prepare the data
This process depends on the application that the data was created in: Access, another database, an email program if you're exporting your address book, etc. Look under the File or Tools menu for an Export option. If you can export to a spreadsheet, do so; otherwise, exporting to a CSV or TXT file is fine.
Here's what choosing File > Export looks like in Microsoft Access.
[IMAGE]
Here's what choosing Tools > Export looks like in Netscape.
[IMAGE]
Exporting to spreadsheets
If you can export to an Excel or OpenOffice.org spreadsheet, you're done.
Exporting to text files
If you can only export to a CSV or TXT file, you have one more step. In OpenOffice.org, choose File > Open. In the Open window, click in the file format list and press T four times or scroll about halfway down to find the Text CSV format. Then, select the CSV or TXT export file and click Open.
[IMAGE]
You'll see this window. Everything should look okay. If it does, click OK; if it does...
To continue reading for free, register below or login
To read more you must become a member of SearchEnterpriseLinux.com
n't, select a different delimiter or adjust other options.
[IMAGE]
Creating or opening a new database
Now, you get to choose what database file you want to bring the data into.
If you've already got a database file
If you've already got a database, open it up by choosing File > Open. The database file can have existing tables in it; you can add new tables to it from the external data or you can append the external data to an existing table if the data has the same columns.
If you need to create a database file
If you don't have a database, you need to create one. Choose File > New > Database. Choose to create a new database and click Next.
[IMAGE]
In this window, just leave everything as is and click Finish.
[IMAGE]
You'll be prompted to save the file. Save it under any name you like and click Save.
[IMAGE]
You'll see the database file open up like this. You're ready to continue to the next step.
[IMAGE]
Bringing the data into your database
This is the simplest part. Open your spreadsheet, and paste it into your new database.
You heard me -- that's all there is to it. Well, there's a little more, but it's very easy. When you paste, you'll create a new table with the pasted data.
Note that you can append to an existing table or create a new table. I'll cover appending later in this section.
Creating a new table
Here's what the process looks like to create a new table.
- First, in the spreadsheet, select all the data you want to bring in. Do include the column headings like Name, Address, etc.
[IMAGE]
- Now move over to your database file. Click the Tables icon on the left, since you're going to be adding a table.
[IMAGE]
- Right-click in the table area and choose Paste.
[IMAGE]
- And you'll see this wizard, which will walk you through the rest of the process.
[IMAGE]
- Name the table and leave the Definition and Data item selected. Also, decide whether you need to create a primary key. If you've already got something like a customer ID that is unique, you don't need to. If you don't, choose to create a primary key. Click Next.
[IMAGE]
- In the next window, insert all the fields you want to bring over. You can use the >> button to add them all. Click Next.
[IMAGE]
- In the Type Formatting window, be sure that the file types and other settings are correct. Then click Create. If you're prompted to create a primary key, you can do it now or do it later (I'll cover that toward the end of this article).
[IMAGE]
- Your table appears in the database.
[IMAGE]
Appending data to an existing table
You approach this the same way as adding. But in the first wizard window, choose to append, and type the name of the table to append to.
[IMAGE]
Click Next. Line up the fields you're pasting with the fields that are already there. You can unmark fields to import and use arrows on both sides to make sure that the fields are lined up correctly. The fields don't have to have the same names: Zip will import correctly into Postal Code as long as the data types and other factors are correct.
[IMAGE]
Click Create, and the data will be added to the table.
Editing and viewing the imported data
Once you've got the data in, you can edit it normally. Here are a few common tasks.
Editing the table definition
In the main database window, right-click on the table name and choose Edit. The window will look something like this. Make changes to field names, types, etc. and save changes.
[IMAGE]
Opening the table to view or add data
In the main database window, double-click on the table name. The window will look something like this. Make changes to data and save changes.
[IMAGE]
Specifying a primary key
Each table must have a primary key. Right-click on the table name in the Tables area and choose Edit. In the window that appears, right-click on the name of the field that should be primary key and choose Primary Key. Close the table, saving changes.
[IMAGE]
Conclusions
This is a pretty straightforward approach -- once you know about it, of course. I think something along the lines of, oh, an option called Import under the Tools or File menu would have been nice; let's hope it'll be in the next version.
But enough "wouldn't it be nice" for now. Returning to the metaphor I began with, you've now got a front door in your database so that you can get the data in. But what if you want to get data out again? Let's say you want to take your table, query or view, and you then want to export it to a spreadsheet or CSV text format. Again, there is no easy way. There is no option under File or Tools. But there is a way. And it's the topic for my next article.
Solveig Haugland has worked as an instructor, course developer,
author and technical writer in the high-tech industry for 15 years, for
employers including Microsoft Great Plains, Sun Microsystems,and BEA.
Currently, Solveig is a StarOffice and OpenOffice.org instructor,
author, and freelance technical writer. She is also co-author, with
Floyd Jones, of three books: Staroffice 5.2 Companion, Staroffice 6.0
Office Suite Companion and OpenOffice.Org 1.0 Resource Kit, published
by Prentice Hall PTR. Her fourth book, on OpenOffice.org 2.0, is coming this summer. For
more tips on working in OpenOffice, visit Solveig's OpenOffice blog.
