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

Open Office 2 Base (database) Tutorials
Importing data

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

Forget any experiences you may have had with Adabas, which came with Star Office, the commercial version of Open Office 1. The Open Office Version 2 database, "Base", aka "ooBase", is unrelated. And remember that Open Office, including Base, is free! But don't let that fool you. It's not new. Big organizations, civil and governmental, are adopting it as their standard office suite... and saving million$.

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.

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

Introduction

In this tutorial, I am trying to show you how to import data from other sources into Open Office Base (aka ooBase) tables.

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.

I wish I could tell you how to move everything from your old application into Base. Sadly, all that I cover here is taking single tables from your old database and moving them into Base tables. I will be rebuilding my own forms, reports, etc, from scratch. But at least we don't need to re-type all of the underlying data!

The plan is simple enough:

Convert old data to a "lingua Franca" called csv.
Fill an Open Office Calc spreadsheet with that data.
Copy the contents of the spreadsheet onto the clipboard
Paste the data into a new table.

So let's get started! But first: Thanks to "JohnV" at the Open Office Forum for the seed which grew into this tutorial.

Data to CSV

"CSV" stands for "Comma Separated Values".

Suppose (as I do!) I had 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 "s are pretty annoying, but the last record shows you why their 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.

From CSV to Spreadsheet

To import a csv file into an Open Office Calc spreadsheet:

Open a spreadsheet, an old one will do, but a new one would be more usual for this sort of work.

The contents of the csv file will be entered into a new sheet, so it doesn't matter what cell is currently selected.

From the menu: Insert | Sheet from file.

Double click on the csv file with the data to be imported.

Tick the relevant separator options. You will see a preview of the import. It will change as you revise the options.

Click okay.. and all should be well!

Do not close the spreadsheet.

From Spreadsheet to Base table

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 one, saved 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.

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.

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 it's name to open it.

That wasn't too painful, now, was it? (Unless dates were involved!)

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.

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.