importing data from excel

[John Burgess]

I have just started to use the database and want to import data from Excel spreadsheets. I keep records for a running club and wnat to be able to produce reports. The data imports successfully but I cannot then edit it - all icons and options are greyed out. If I save the file then look at the file in my documents the file icon is not the same as if I create a new database. The same is true when I import Excel data directly into calc. How can I get round this problem?

[Villeroy]

All non-databases are read-only (spreadsheet, text, address book). Edit the Excel file in Calc or convert to some other format. Plain dBase (*dbf) may be an option. dBase imports with write access, you can create indices but no relations between tables.
The native database format utilizes an integrated version of http://hsqldb.org
You get this kind of database with data embedded in the Base document when you create a new databse from scratch. There is an import wizzard which pops up when you drag tables into a database's tables container or when you call "Paste..." from the context menu of a database's tables container.

[williaba]

Hi John,
May I ask how you performed the import of data from your spreadsheet into a database table?
Regards, Brian.

[Villeroy]

Hi John,
May I ask how you performed the import of data from your spreadsheet into a database table?
Regards, Brian.

Indeed, this is the important information he left out. In my previous reply I assumed that he performed the following steps:
File>New>Database...
[X]Connect to existing database ("I have my data already stored somewhere")
Type: Spreadsheet (specify your spreadsheet file...)
[X]Register the database

You end up with what I call a pseudo-database since it imports data from a spreadsheet as if it where a "real database". The used ranges on the sheets and Calc's database ranges appear as database tables.
You can add queries (extracting sub-sets of your data) and reports (for pretty printing) to this database. Input forms won't be useful since pseudo-databases lack database specific row-pointers. They provide their data in read-only mode. You still have to open the underlying spreadsheet in a spreadsheet application like Calc for edit.
If you have registered the new database you can use it's tables and queries from the data source window (hit F4 in Writer or Calc) and you can use it as a source of mail merging and other stuff.

Utilizing a copy&paste or drag&drop-approach you can import spreadsheet data into "real databases" with read-write access.

[scarter]

Hi John,
May I ask how you performed the import of data from your spreadsheet into a database table?
Regards, Brian.

The easy way to import spreadsheets into a database table is to open the spreadsheet in the Calc application. Select everything (click on the upper left hand cell, or Edit > Select All). Copy everything to the clipboard by using Control C if using Windows, or Edit > Copy. Switch to Base. In the left hand pane, click "tables". Put your mouse over the list of the tables in the database. Paste what you've copied (Edit > Paste or Control V). An import wizard will open up asking you how to treat the spreadsheet information. I'm sure there's a tutorial about this, which can lead you through the questions from the import wizard; I found it pretty simple to figure out.

[Villeroy]

Well, the copy/paste import is easy if your spreadsheet (or whatever) contains consistent data. The import fails when there is a single text in a numeric column, or a duplicate in the primary index.
All dates will be 2 days off. You may import an adjusted date or ISO-date string instead of the original dates: =$A1-2 or =TEXT($A2-2;"YYYY-MM-DD") [A2 having the date]. As another option you may run an UPDATE query on the dates after import.