Let's say you've got a bunch of data: addresses, or lists of your DVDs, or whatever. It's not world-changing high-transaction information, but you want it in a good storage format
Do you create a spreadsheet and put your info in that, or do you go straight for the higher-end solution and create a Base database?
The good news, it doesn't matter that much. Generally, I would recommend that you start with the spreadsheet, then if necessary go to the Base format. The transition isn't a matter of right-clicking and choosing Convert but it's not bad. Pasting, essentially.
Follow these directions to get your data in a spreadsheet, then make a database file that points to that spreadsheet. Your spreadsheet might be called mymailinglist.ods and your database file might be called mailinglistdatabase.odb. Note the different file extensions. (You can call the files Bob.ods and Mary.odb if you want; these are just examples.)
Then if you find you want to do more complex operations like queries, data entry forms, etc. follow the directions in this article, also below, to turn your spreadsheet into a true Base database. You create a new database file, like mynativedatabase.odb, and paste the contents of your spreadsheet into it. You will then use mynativedatabase.odb instead of mailinglistdatabase.odb.
Turning a spreadsheet into a native Base database
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.
In this window, just leave everything as is and click Finish.
You'll be prompted to save the file. Save it under any name you like and click Save.
You'll see the database file open up like this. You're ready to continue to the next step.
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.
Now move over to your database file. Click the Tables icon on the left, since you're going to be adding a table.
Right-click in the table area and choose Paste.
And you'll see this wizard, which will walk you through the rest of the process.
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
In the next window, insert all the fields you want to bring over. You can use the >> button to add them all. Click Next.
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).
Your table appears in the database.
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.
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
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
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.
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
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.
I'm not a programmer or an advanced DBA, and I don't plan to become one. But I get--well, not huge numbers, but definitely significant numbers of jobs I need to turn down because the technical end of the spectrum isn't my field. Examples include automation of mail merges, for instance -- create a way for users to just click a button to run a pre-set-up mail merge. Who wouldn't want that feature? (Has anyone done this yet?)
I'd like to be able to refer people to consultants who do what I don't; if you're an OpenOffice.org scripting/database consultant currently doing these types of things, please do leave a comment with your web site. I know there's a list of consultants on the OpenOffice.org web site, as well, but I'm not sure how current those are or how specifically applicable to these types of jobs.
I've been pointing folks to the instructions in this blog, but I wanted to pare things down so that I have one blog entry, just one, that shows only how to make your spreadsheet into something you can use in mail merge letters, label mail merges, envelope mail merges, etc.
Overview of How Mail Merges Work in OpenOffice
First: To print labels from a list of addresses, you should put them in a spreadsheet. (You don't have to since text files, address books, and regular databases are fine for holding your data for mail merges, too. But spreadsheets are a simple approach.) Put headings across the top like Firstname, Lastname, etc.
Then: To make your spreadsheet into something that your mail merge document (labels, Christmas letter, etc.) can talk to, you need to make a little database file that points to your spreadsheet.
Finally: You make your mail merge document (labels, Christmas letter, etc.) and have it point to your database.
That's the summary. Here are the specifics.
Instructions for the First Step, Putting Together Your Data in a Spreadsheet
Here's what your data should look like. The columns you use are up to you. Put in whatever data you will need. Put City, State, etc. all in different columns. Be sure to put the label at the top for what the field is: Name, etc. This is important.
Here's a sample file. Right-click on the link and choose to save it to your computer.
Instructions for the Next Step, Making Your Spreadsheet Into a Database
You need to make a database file that points to the spreadsheet.
1. Choose File > New > Database.
2. Make the selection shown, with Spreadsheet as the format.
3. Click Next.
4. Specify the spreadsheet file. Each SHEET in that spreadsheet will be a table in your database.
5. Click Next.
6. Umark the option to open the database for editing. You can open it; you just don't have to.
7. Click Next.
8. Save the data source (aka database) under a name that will help you remember what it is. This is the name that you will see in your lists of databases.
You're done with the database.
Instructions for Making Labels or a Letter and Pointing It at a Database
To make labels that point to the database you created, follow these instructions. You've already done the database so
Here's a tip from Dave Richards -- you can make the gallery float, so that you can position it in a more convenient place in your work area.
What the Gallery Generally Looks Like
Choose Tools > Gallery, and you get the clip art and categories on your system. (You can click on each of these images to see a bigger version.)
Adjusting the Amount of Space for the Gallery
You can make the gallery take up less, or more, room, by moving your mouse over the small dotted portion of the border, as shown. When you mouse turns into a double-ended arrow, drag up or down.
Then the Gallery portion is smaller or larger, depending on your adjustments.
Floating the Gallery
However, if you'd like your Gallery to be a floating pane like some of the other windows in OpenOffice.org, you can do that. Click and hold down on the gray part at the top, and drag down into the middle of the document.
When you see a rectangle with a gray dashed border, release. The Gallery will be a floating pane.
You can resize and move the pane as you would normally for any floating pane.
Redocking the Gallery
Now, here's the question -- how do you get the Gallery back to where it was, docked at the top?
Not easily. ;>
You can drag the Gallery easily to the left, right, or bottom, and redock it there. Just drag toward the border and when you see the gray dashed border again, release and the Gallery will be docked. (To turn off the Gallery, just choose Tools > Gallery again.)
You can redock the Gallery at the top, it's just really tricky. There is a very small area where you can position the Gallery floating pane and the gray dashed border will appear. Here's the area where it is. Drag the Gallery through it slowly; dragging down usually works better for me than dragging up.
It's been a while since I sat down with some big sheets of paper and my crayons, and just colored. (I had that 64-color set of crayons with the extra-glamorous sharpener on the back....got it for Christmas my sixth birthday, I think, and I LOVED it. Sky Blue was my favorite.)
What I do these days, though, although the old set of crayons is actually on my shelf of cute collectibles along with my Star Trek popup book and a picture my parents the day they met, is to waste many fun hours with Draw.
One of the hidden but dramatic and fun features in Draw is the ability to create and twiddle around and format text in 3D.
Creating the text box
Find the text tool on the Drawing toolbar.
Click it, then move your mouse to the work area and draw a text box.
Immediately type in the text box
Converting the text box to 3D
Click on the text box where the border is, so that you get the green handles as shown. Then right-click on the border between or on the green handles and choose Convert to 3D.
The text will be converted.
Apply a lighter color if it's black so that you can see it better.
Modify the text by dragging one of its handles, so that the proportions are better.
To rotate the text in 3D, click in the middle of the text once; you'll see round red handles as shown and the mouse will change when you position it over a handle.
Drag a handle to move the text in 3D.
Click off the text, then once on again if you want to get the green handles back so that you can resize or re-proportion the text.
Applying standard fills
You can apply colors, but also gradients, hatchings, and bitmaps to the text. Think of it as a shape now, not text (you can't retype it at this point).
Here's a gradient.
Here's a bitmap.
Applying official 3D formatting
To apply 3D formatting, generally you should have a plain color applied, though there's no real cut-and-dried rule. Right-click on the text and choose 3D Effects.
Select from the many options, then click the green Apply checkmark to apply effects.
To export your text so you can make it into a GIF or similar graphic, see this post.
The City of Heerenveen (NL) uses and relies on an OpenOffice.org Web 2.0 work environment based on O3Spaces Workplace. By
implementing the newly released O3spaces workplace, including the
integrated template and extension, the City of Heerenveen can now
benefit from document management, collaboration, template management as
an integrated part of their OpenOffice.org environment.