Convert Spreadsheet To Database

Converting an Open Office Calc spreadsheet to a Base table.

(Based on http://sheepdogguides.com/fdb/fdb1imp1.htm)

Remember that a database may consist of several tables, forms, queries, reports.

You are 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".

1. Go back to the spreadsheet holding your data. If there are no column headings, insert a row above the top of the data & type column names into the new cells. The contents of these cells will be used to name the fields.
2. Click at the intersection of column & row heads to select all of the data, including the field name cells.
3. Copy the data to the clipboard (using ctrl-c on Windows, cmd-c on Macs)
4. Go back to the database. Select "Tables" in the "Database" pane.
5. Right-click in the "Tables" pane (bottom right). Click on "Paste"
6. If your data does not contain a primary key already, BE SURE TO TICK "Create primary key". Leave "Data and definitions" selected.
7. Click Next.
8. 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 move them easily.
9. Click Next.
10. In the "Type formatting" page, you may be able to leave most things unchanged, BUT: If you are importing any numerical data, change the field type from VARCHAR to INTEGER or DECIMAL.

• If you specify too few characters for a text (VARCHAR) field, then any records which are too long will be ignored.
• If you have some numbers which are not integers (whole numbers), for instance amounts of money, use "Decimal", AND set the number of decimal places (probably '2').

IMPORTANT: If your data already contains a field you wish to use as the primary key, change it's field type to INTEGER then right click on the field name and select 'Primary Key'.

Click "Create", and, after a moment, your table should be done. Double click on its name to open it.

---

Related pages:

Category: ICT

Author: Mr Kershaw