Problems importing via spreadsheet

[ptoye]

I have an database in an ancient format (Lotus) which I converted to spreadsheets to import into Base.

Several problems:

1) One of the tables has a key which I want to define as a primary key. When I define it I get an error with this message:Constraint not foundtable has no primary key in statement [ALTER TABLE "Regions" DROP PRIMARY KEY]. This is illogical: creating a primary key should not require the dropping of a non-existent one.

I need the values in this field to be referred to by other tables. I suppose I could always create the primary key and never use it, but this seems silly.

2) Some of the fields are logical fields with "Entry required" set to "No". But the blanks in the spreadsheet are imported as "Yes" rather than "no value", whch is totally wrong.

Any idea how to get round these problems?

I'm running OO 3.2.1, if this makes any difference.

[Villeroy]

I assume that you are working with an embedded HSQLDB documented in http://hsqldb.org/doc/guide/ch09.html

For some reason I don't know it seems to be impossible to turn an existing field into a PK. The command line does not work neither which indicates that this has nothing to do with Base.
Don't create tables by copy&paste. Create empty tables in the GUI or using the command line (Tools>SQL...) including (auto-)ID, indices and all field types. Then you append data from elsewhere through the clipboard.

Your booleans should import correctly when the field does not require entries with default value <none>.
This can be achieved in the GUI or with menu:Tools>SQL...
ALTER TABLE "Table" ALTER COLUMN "Bool" BOOLEAN;
menu:View>Refresh Tables [always when we used the command line]

I copied some values from a spreadsheet into a boolean field and it converts all text (including the empty string "") as well as empty cells to Null. Numbers become booleans where zero becomes False and any other number becomes True.

[ptoye]

Thanks. It seems to work better.

What seem to be bugs in the underlying database don't fill me with confidence, though....

I may have been wrong about the boolean import - on my terminal the greyish "null" values look very like black, and the first two screenfuls of that column were all null. Why Base doesn't put a blank for null is beyond my comprehension. There must be a reason.

[Villeroy]

Indeed, the check boxes in Null state look too close to boxes with True state.
The underlying HSQLDB is not buggy. You can rely on it like many Java and database developers do. Modifying the field type of a populated field is always a risky operation in any type of relational database.
Once you deleted all your test data you can add the PK easily to the empty table.
You can create a blank, unlinked table with all keys and correct field types, then drag the old table onto the new one, choose "Append Data" and remove the old one.
Nevertheless, most experienced users consider the embedded HSQLDB as experimental, well enough for demo purposes, too slow, insecure, unsafe for serious use.

Most valued forum member DACM wrote very knowledgeable, nevertheless comprehensible, articles in this forum and the other one. I take http://user.services.openoffice.org/en/ ... 67#p162653 as a link collection to everything related to embedded HSQLDB, its problems and more than one solution to convert your embedded database design into something which is as reliable and powerful as any other database.

[rlmrdl]

I'm not sure if this is related but I used to use a CSV in Writer to produce address labels but now that there is a tool for creating labels that uses only database files, I am a bit flumoxed.

How do I take the csv file and either save it as a database file (the "save as" offers dbase [dbf] but doesn't seem to create a file that the label tool recognises as a database) or import it into a new database with the same field names?

Happy new year.