Starting a new database: spreadsheet or native Base database?

Let's say you've got a bunch of data: addresses, or lists of your DVDs, or whatever. It's not world-changing high-transaction information, but you want it in a good storage format

Do you create a spreadsheet and put your info in that, or do you go straight for the higher-end solution and create a Base database?

The good news, it doesn't matter that much. Generally, I would recommend that you start with the spreadsheet, then if necessary go to the Base format. The transition isn't a matter of right-clicking and choosing Convert but it's not bad. Pasting, essentially.

Follow these directions to get your data in a spreadsheet, then make a database file that points to that spreadsheet. Your spreadsheet might be called mymailinglist.ods and your database file might be called mailinglistdatabase.odb. Note the different file extensions. (You can call the files Bob.ods and Mary.odb if you want; these are just examples.)

Then if you find you want to do more complex operations like queries, data entry forms, etc. follow the directions in this article, also below, to turn your spreadsheet into a true Base database.  You create a new database file, like mynativedatabase.odb, and paste the contents of your spreadsheet into it. You will then use mynativedatabase.odb instead of mailinglistdatabase.odb.

Turning a spreadsheet into a native Base database

Creating or opening a new database

Now, you get to choose what database file you want to bring the data into.

If you've already got a database file
If you've already got a database, open it up by choosing File > Open. The database file can have existing tables in it; you can add new tables to it from the external data or you can append the external data to an existing table if the data has the same columns.

If you need to create a database file
If you don't have a database, you need to create one. Choose File > New > Database. Choose to create a new database and click Next.

In this window, just leave everything as is and click Finish.

You'll be prompted to save the file. Save it under any name you like and click Save.

You'll see the database file open up like this. You're ready to continue to the next step.

Bringing the data into your database

This is the simplest part. Open your spreadsheet, and paste it into your new database.

You heard me -- that's all there is to it. Well, there's a little more, but it's very easy. When you paste, you'll create a new table with the pasted data.

Note that you can append to an existing table or create a new table. I'll cover appending later in this section.

Creating a new table
Here's what the process looks like to create a new table.

1. First, in the spreadsheet, select all the data you want to bring in. Do include the column headings like Name, Address, etc.

   

2. Now move over to your database file. Click the Tables icon on the left, since you're going to be adding a table.

   

3. Right-click in the table area and choose Paste.

   

4. And you'll see this wizard, which will walk you through the rest of the process.

   

5. Name the table and leave the Definition and Data item selected. Also, decide whether you need to create a primary key. If you've already got something like a customer ID that is unique, you don't need to. If you don't, choose to create a primary key. Click Next.

   

6. In the next window, insert all the fields you want to bring over. You can use the >> button to add them all. Click Next.

   

7. In the Type Formatting window, be sure that the file types and other settings are correct. Then click Create. If you're prompted to create a primary key, you can do it now or do it later (I'll cover that toward the end of this article).

   

8. Your table appears in the database.

   

Appending data to an existing table
You approach this the same way as adding. But in the first wizard window, choose to append, and type the name of the table to append to.

Click Next. Line up the fields you're pasting with the fields that are already there. You can unmark fields to import and use arrows on both sides to make sure that the fields are lined up correctly. The fields don't have to have the same names: Zip will import correctly into Postal Code as long as the data types and other factors are correct.

Click Create, and the data will be added to the table.

Editing and viewing the imported data

Once you've got the data in, you can edit it normally. Here are a few common tasks.

Editing the table definition
In the main database window, right-click on the table name and choose Edit. The window will look something like this. Make changes to field names, types, etc. and save changes.

Opening the table to view or add data
In the main database window, double-click on the table name. The window will look something like this. Make changes to data and save changes.

Specifying a primary key
Each table must have a primary key. Right-click on the table name in the Tables area and choose Edit. In the window that appears, right-click on the name of the field that should be primary key and choose Primary Key. Close the table, saving changes.

Conclusions

This is a pretty straightforward approach -- once you know about it, of course. I think something along the lines of, oh, an option called Import under the Tools or File menu would have been nice; let's hope it'll be in the next version.

But enough "wouldn't it be nice" for now. Returning to the metaphor I began with, you've now got a front door in your database so that you can get the data in. But what if you want to get data out again? Let's say you want to take your table, query or view, and you then want to export it to a spreadsheet or CSV text format. Again, there is no easy way. There is no option under File or Tools. But there is a way. And it's the topic for my next article.

---

---

Comments

[Michael]

Your comment:
Opening the table to view or add data
In the main database window, double-click on the table name. The window will look something like this. Make changes to data and save changes.

So why doesn't it? The insert button isn't available, the data is uneditable. Data can be appended from the clipboard, but none of the table entries can be edited. So what's up with it?