AUTHOR'S MAIN SITE   > > > > >   TABLE OF CONTENTS for Open Office database tutorials.

Open Office Base (database) (ooBase) Tutorials
Manipulating some data... moving it where it's needed, in the right form.

You may find that the database being shipped with OpenOffice (ver.2 and higher) delights you as much as it has me. This page tries to help you use it.

Forget anything you may have heard about Adabas, which came with Star Office, the commercial version of Open Office 1. The current Open Office's database, "Base", aka "ooBase", is unrelated. And remember that Open Office, including ooBase, is free! But don't let that fool you. And it's not new. Big organizations, government and civilian, are adopting it as their standard office suite... and saving million$, but still Getting The Job Done.

There's more about ooBase in the main index to this material.

This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window. With most browsers, pressing plus, minus or zero while the control key (ctrl) is held down will change the texts size. (Enlarge, reduce, restore to default, respectively.) (This is more fully explained, and there's another tip, at my Power Browsing page.)

Page contents © TK Boyd, Sheepdog Software ®, 3/06.



A case study: Working with data from outside Open Office, and creating something useful from it _______________

In another essay, I wrote up parts of my database for keeping track of my investing decisions, and the history of changes to my holdings arising from splits, spin offs, and the like.

In this document, I'm going to relate a very "real world" (i.e. messy!) need and solution.

I needed a very simple document: A two column table with companies names in the first column, and the number of shares I owned in each in the second column.

Oddly enough, I didn't have an easy way to get this! I had a way to get a more complex table. It was similar to the one I needed, but had more than two columns: One for each of my brokerage accounts.

The table I wanted had to be in a form that I could import into a spreadsheet. To that, I added a column (by hand) with the price per share that each company was worth on a particular date, and then I had the spreadsheet multiply the number of shares I had by the value per share, so that I could see which were my important holdings, and which holdings mattered less.

Yes... there are ways the whole thing could have been done within the database. I didn't want that, though. I wanted the spreadsheet.



How it was done

So. What was available to me, to work with?

One thing I had was a .CSV file that contained lots of stuff, but for our story only two of the fields mattered: Company name and shares added or removed.

CSV file: A "lingua franca" for database and spreadsheet data. Most databases and spreadsheets normally save their data in weird, wonderful, and often secret "codes" which are fine when you want to use the data with the application that created it, but tedious otherwise.

While a CSV file usually throws things (like formatting) away, it does have the virtue of being "readable" by just about any application.

I've written several pages about CSV, if you want to learn more One is just a simple statement of what CSV is. From there you can access other pages with information about CSV and OpenOffice in general. Many elements of what CSV files are can be inferred from the material below.

My data came from Paradox... the database program I use for things that predate the availability of ooBase. Paradox makes it easy to export data to the CSV format.

I started up ooCalc, and asked to open a file. Because the name of the file I was opening ended ".csv", ooCalc (correctly) assumed this was a CSV file, and so an extra dialog popped up along the way of opening the file.

That dialog allowed me to tell ooCalc a few things about the exact "flavor" of CSV used in the file I was opening. What special character had been set aside to indicate the end of one field, the start of another? Were text strings enclosed in quotes? And so on.

Because ooCalc is well written, it was easy to get those questions answered correctly, and, a moment later, I had a spreadsheet which looked much like an ooBase table. I had rows with records, each made up of columns holding field data.

Remember I said that the source of my data had "extra stuff"? I just deleted those columns from the spreadsheet.

I was left with just two columns- company name, and a number of shares.



A Good Start Made, Then.... BUT!

But! There was frequently more than one line for any one company. The spreadsheet showed the whole history of my investing! For example, there was one line saying KTEK 100 and another saying KTEK -100, reflecting the fact that years ago I bought 100 shares of KTEK, and not long after sold them again, since when I've had nothing to do with the company.

I needed to consolidate everything, and throw out all of the things like KTEK where the net of my activities to date is no shares in a particular company.

At this point, I felt ooBase could help, and I was right Nothing could be easier... when you know how!

Remember: By now, my spreadsheet consists of two columns, many rows.

I inserted a row at the top of the spreadsheet, and put labels on the columns: Co (for "company") and Shrs, for "number of shares".

I saved my work from time to time, of course, taking care to save it in the usual ooCalc format, as an .ods file. (You ensure this by setting the "save as type" parameter during the "Save As" dialog.)

I then selected ALL of the material in the spreadsheet, including the column headings I'd recently inserted.

Copied what I'd selected.

Started up ooBase, creating a new database along the way. In its own folder, unregistered.

Once the main project manager window appeared, I made sure the "tables" task was selected, and then right clicked in the "tables" pane

Clicked the "Paste" option.

Chose StockHldgs as the table name, and said that what I was pasting consisted of definition and data.

In the next dialog, "Apply columns", I used the "> >" button to put both the Co and Shrs columns in the right hand, "to be imported", window.

Clicked Next

Changed the Shrs Field type to "Decimal- 4 decimal places"

Clicked Create.

ooBase quite properly complained about the lack of a primary key, but gave me a "create one now" button, which I used.

Brilliant! My data was now in ooBase.



Rabbit caught. Make stew....

Next I wanted to "boil it down."

Used the wizard to create a query.

Took just the Co and Shrs fields from the table for the query.

Sorted by Co

Didn't set any search conditions.

Set "Detail or summary" to create a summary query, using "get the sum of" the field "Shrs"

Didn't use the aliases feature.

The immediate result was a shock: It gave numbers of shares, but no company names!

Closed the query. Reopened it, but in design mode. I.e. I right clicked on the query's name in the project manager window, and then selected "Edit"

Ah ha! The company field was indeed part of my query, but the "visible" box wasn't ticked. Ticked it. Saved query. Re-ran query. Got just the results I wanted! (Well, the columns weren't in the order I'd expected, but that's not going to be a problem.

Why must a man's reach exceed his grasp?

I then spent many hours trying to do something which should have been simple. Bah. Will tell you about it another time.

However, it turns out that ooBase and ooCalc are exceedingly clever. I would never have guessed that the following is possible, but something at oooForum.org tipped me off. (give them a try. You don't need to register to read old posts.)

I navigated to the main project management window of ooBase. Clicked on "Queries" in the "Database" pane. (The one filling the left of the window.)

Once I'd done that, I could see the query I'd created and saved. The one we were discussing a moment ago.

I did not open it. I merely right clicked, and then clicked on "copy".

I then navigated to an empty ooCalc worksheet, and right clicked on a cell vaguely in the upper left. Sure enough, there was a "paste" option. When I clicked it, the ooCalc worksheet filled with the table that the query produced. Brilliant!

I had what I wanted: A spreadsheet with one row per company, and my holdings in that company, the total across all my accounts, listed next to the company name.

The rest of working out which were the important companies was so pedestrian that I won't bore you with it. I didn't use anthing clever to input the current share prices. I just did that "by hand".

I hope you found that usefull



Editorial Philosophy

I dislike 'fancy' websites with more concern for a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Of course, an attractive site WITH content deserves praise... as long as that pretty face doesn't cost download time. In any case....

I am trying to present this material in a format which makes it easy for you to USE it. There are two aspects to that: The way it is split up, and the way it is posted. See the main index to this material for more information about the way it is split up, and the way it is posted.


Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However.. this doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!

PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE


If you liked this ooBase tutorial, see the main index for information other help from the same author.

Editor's email address. Suggestions welcomed!     - - -    Want a site hosted, or email? I like 1&1's services.




Valid HTML 4.01 Transitional Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org


One last bit of advice: Be sure you know all you need to about spyware.

. . . . . P a g e . . . E n d s . . . . .