Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.orgYou 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.
In this tutorial, you should learn how to import data from other sources into Open Office Base (aka ooBase) tables. There are other articles available explaining 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... Base can, up to a point, work with data created by other RDMSs... but you are going to encounter fewer hassles if you take the trouble to rebuild your database in Base.
You may come across things that tell you that you can "read Access databases". True. But don't get all excited. All you can do is read what is in 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 Base tables. As with Access, in most, if not all, cases it is a matter of rebuilding the forms, queries, reports, from scratch. But at least all of the underlying data does not need re-typing!
Let's get started! But first:
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 what CSV files are.
Suppose (as I do!) I have a database with the names of books I've read, author, the number of pages, and the year I read them. That data, in CSV form, might look like....
"Lord of the Rings", "Tolkien", 1065, 1969 "Angel Maker", "Pearson", 465, 1998 "Lion, Witch, Wardrobe", "Lewis", 256, 1970
All those quote marks ("s) are pretty annoying, but the last record shows you, I hope, why they are needed.
Whatever database your data is in at the moment 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 bypass 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, 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 with ooCalc's "File | Open", or
ii) You can open the CSV file as a new sheet within an already open workbook. (The latter is accomplished with "Insert | Sheet from file."
(If you adding the data to an already-open worksheet with "Insert | Sheet...", the contents of the CSV file will be entered into a new sheet, so it doesn't matter what cell is currently selected.)
When the dialog comes up to allow you to choose the file to load, double-click on the CSV file with 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 the data we were using for examples earlier, and the datagrid has something like...
2,abc,Fru....
... all in one column, then look at the "Separator options".
When things are looking pretty good, take a close look. Are things like "Partridge, in pear tree" being SPLIT across two 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 "Partridge" and "in pear tree" between two fields. Even Baldrick would concede "cunning", surely?
Once you've got those right, and 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".
To change a column's data type, click on the column, and then use the pull down box next to "Column type".
If you set a column type to "Hide", it won't, during the import process, be put on the spreadsheet at all. It isn't "hidden" in the sense of hidden columns on the spreadsheet... Rather, that column of data in the CSV file is excluded from the import process.
If you leave a column's type as "standard", ooCalc will try to convert things to numbers as they are imported. When a specific field in a specific record cannot be imported as a number, it will be imported as text. You probably don't want the resulting mixture, so set the column's type to something other than "standard", depending on the nature of the data in the field.
If the CSV file has, say 3.99 in it, and the spreadsheet is defaulting to displaying numbers without decimal places, you may SEE "4"... but if you tweak the sheet's formatting, the "3.99" will appear.
If a column holds dates, its column type should be changed accordingly.
I haven't addressed a tickbox that arises if you use "Insert | Sheet from file".. the "Link" tickbox. 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 incorrect 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.
You are merely creating a new table. Either open an existing database, or 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"
BE SURE TO TICK "Create primary key". Leave "Data and definitions" selected.
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, BUT: If you have any numerical data in what you are importing, it would be best to change the default field type which you will otherwise get for those fields.
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, but 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 . . . . .