Bookmark this on Delicious
Recommend to StumbleUpon
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 ®, 2/06-3/10
In this tutorial tries to help you move data from other database managers into Open Office Base (aka ooBase) tables. Other tutorials explain how to move data between Open Office applications and how to export data from an Open Office application for use in an external application.
You may find that you don't need to do imports... ooBase can, up to a point, work with data created by other RDMSs... but in the long run, you are going to encounter fewer hassles if you take the trouble to rebuild your database in ooBase.
You may come across things that tell you that ooBase can "read Access databases". True. But don't get all excited. All you can do is read the tables. The Access forms, queries and reports will not work via ooBase.
I wish I could tell you how to move everything from your old application into ooBase. Sadly, all that I cover here is taking single tables from your old database and moving them into ooBase tables. In most, if not all, cases it is a matter of rebuilding the forms, queries, reports, from scratch, as you would do to move a database from Access. But at least you don't have to re-type all of the underlying data!
Let me mention one thing this tutorial will not show you: It will not show you how to append data to an existing ooBase table. I prefer to do things one step at a time. If I needed to append data from outside ooBase to an existing ooBase table, I would first import that data into a temporary ooBase table, and then append the contents of the temporary table to the existing table.
Let's get started! But first I'd like to say "thanks" to JohnV at the Open Office Forum for the seed which grew into this tutorial.
"CSV" stands for "Comma Separated Values". I've produced a separate page with a general discussion of CSV files.
From long before ooBase came along, I have had a database with the names of books I've read. It was in Borland's Paradox, and recorded the title, author, the number of pages, and the year I read each book. Converted to CSV form, that data looks like....
"Angel Maker", "Pearson", 465, 1998 "Lord of the Rings", "Tolkien", 1065, 1969 "Lion, Witch, Wardrobe", "Lewis", 256, 1970
All those quote marks ("s) are pretty annoying, but the last record should show you why they are needed.
Whatever database currently holds your data should have a tool to export the data to a CSV file. Certainly Paradox does. I can't tell you too much about this part of the exercise. You'll have to get the CSV export done by yourself! If you can't get directly to CSV, you may find that your other application lets you move a table to a spreadsheet. If you can get it from there into the Open Office Calc spreadsheet somehow, then you won't need the CSV file. That is all we are going to use it for. You may even be able to skip putting your data into the Open Office Calc spreadsheet, copying directly from your other spreadsheet.
The tab character is sometimes used in place of a comma in export files. If that is all your application offers, it is worth a try; it would probably work.
To import a CSV file into an ooCalc spreadsheet:
Open a spreadsheet. An old one will do, but a new one would be more usual for this sort of work.
You can either...
i) Open the CSV file into an new ooCalc workbook by using ooCalc's "File | Open"
....or....
ii) Open the CSV file as a new sheet within an already open workbook by using ooCalc's "Insert | Sheet from file".
(Two "details"...
... end of "details".)
When the dialog comes up to allow you to choose the file to load, double-click on the CSV file which holds the data to be imported.
In either case, you will be presented with a fancy dialog box. At the bottom of it you will see a datagrid. It will probably already hold a fairly good "shot" at the table you want.
If things are "bunched up", e.g. if you were importing my "books read" data, and the datagrid has something like...
Angel Maker Pearson 465
... all in one column, then look at the "Separator options". As you change them, you should see the data distribute across the columns differently.
When things are looking pretty good, take a close look. Are things like "Lion, Witch, Wardrobe" being SPLIT across three columns? If so, you need to fix the "text delimited" setting. In the example above, we used commas for "Separated by", i.e. the field separator, and the quotes mark (") for the "Text delimiter". (In which case, commas inside a pair of quotes are "hidden", and not used to split "Lion" and "Witch" and "Wardrobe" between three columns. Even Baldrick would concede that's "cunning".
Once you've got those settings right, i.e. things are being sorted into fields properly, look at the data you are importing. Are any fields with only digits NOT numbers? For instance, you might have a "serial number" or "telephone number" field. While the serial "number" 529526 is made up of digits, it is not a number in any serious sense.... and serial number "00023" will get converted to "23", which you may not want. Also, you're never going to do arithmetic with a telephone number, so don't let the automatic data typing declare that field numeric. Set the column type of such fields to "text".
Some odds and ends...
I haven't addressed the "Link" tickbox which arises if you use "Insert | Sheet from file". I'm not going into detail, but I'll advise you to keep your life simple: Leave that unticked. Ticking it creates some "magic", but magic you may like as much as the sorcerer's apprentice liked the magic he unleashed. With the box unticked, you'll import the data as it is at the moment in the CSV file concerned. By ticking the "link" box, if you know what you're doing, and you do it right, the information in the worksheet you are creating will stay in sync with the CSV file you created it from.... even incorporating changes to the CSV file made AFTER the worksheet was created. But get it wrong, and changes which you thought would percolate though don't, and you are working with bad data....
Click "Okay"! The spreadsheet should fill with the data, nicely set out in a grid, like an ooBase table.
Do not close the spreadsheet.
Remember that "a database" may consist of several tables, forms, queries, reports. What you may think of as "a database" may merely be a table.
You are merely creating a new table. Either open an existing database, or, if appropriate, start a new database in the usual way. If starting a new one, select "Register / Open database", but do not select "Create tables with wizard". I created a new database and saved it in....
C:\My Documents\FreeDB\FDB006\
...as FDB006.odb
Go back to the spreadsheet holding your data. Insert a row above the top of the data. Type column names into the new cells. The the contents of these cells will be used to name the fields.
Select all of the data, and the field name cells. (Ctrl-A is the easy way to do this. Don't be alarmed by the fact that the whole sheet gets selected.... unless you have "stuff" on the sheet besides the data (and headings).)
Press ctrl-c (Shortcut for copy)
Go back to the database. Select "Tables" in the "Database" pane.
Right-click in the "Tables" pane. Click on "Paste"
Select "Data and definitions" selected, if it isn't already selected.
Give the table a meaningful name
Be sure to tick "Create primary key".
Click Next.
The "Apply columns" page lets you decide which columns you want in the new table. You will probably want them all, and the >> button allows you to have them easily. You can delete superfluous columns later easily; you can't add missed columns easily.
Click next.
In the "Type formatting" page, you may be able to leave most things unchanged. Note that in this dialog, you are setting properties for each of the fields in the table. As you change the field selected in the left hand column, you see that field' properties in the rest of the dialog box.
Setting the data type properly is especially important. If you have any numerical data with which you want to do arithmetic (for example, a "price" field in an inventory system), it would be best to change the field type for those fields. However, as explained earlier, you will probably want to use one of the "text" types for serial "numbers", etc.
Be careful if you decide to try to make any changes to the primary key field. If you aren't sure that you know what you are doing, it would be best to leave the settings as ooBase made them.
If you specify too few characters for a text field, then any records which are too long will trigger a message during the "create table" phase, and I'm afraid the records are just dropped. You can use the database's ID field's value as a guide for finding the lost records in the spreadsheet copy of the data.
If you have some numbers which are not integers, for instance amounts of money, the type which would work for me was "Decimal", AND I had to raise the number of decimal places above the default "0".
If you have dates to import, they are probably going to be their own special sort of nightmare.... the usually are, anyway... across all applications! But they "should" work, and sometimes will. Experiment, but check your data imports carefully! Among other things, be sure that 1/2/2006 comes out the way you want it. 1st of Feb or 2nd of Jan? It can mean either, depending on what part of the world you are in, and computers get confused. Having said all of that, I should add that I have seen dates work... with a following wind!
Click "Create", and, after a moment, your table should be done. Double-click on its name to open it.
That wasn't too painful, now, was it? (Unless dates were involved!)
This is one of at least four pages discussing CSV files in my ooBase tutorials. In this page, you were shown how CSV files could be used to import data from external, non OpenOffice applications.
... and of course there's also the site's main menu!
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.
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.
Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org
. . . . . P a g e . . . E n d s . . . . .