I'm importing a table from LibreOffice Calc to a new embedded Libreoffice base database. I select the data, copy & paste it, the wizard pops up and I select use first line as column names.
I then select all the fields and move to the third step of the import wizard. I can right-click my ID field & make it the primary key, fine. The problem is that if I set the field data types to anything other than double or varchar the import crashes with error "incorrect type for setstring". I want to use integer and date types - how am I supposed to import them?
If I leave all fields at either double or varchar and try to edit the table later it won't let me change data types. Same problem if I first define the table and then append records.
This would be easy if I was making a new database from scratch, but I have lots of existing records to import. I need to preserve the keys to set up relationships with other tables.
I've tried both HSQLDB and firebird embedded.
This bug stops me from ditching Microsoft Access in favour of libreoffice base. Can anyone suggest a work-around?
Thank you Jim K for your response, this solves half the problem.
I have found two problematic columns - a date field and a boolean field. Although Calc does understand that my date field is a date, it crashes the import to Base as described. I then told Calc to display the date as YYYY-MM-DD and the import to Base worked perfectly.
The next problem is the boolean (YES/NO) field. A blank cell in Calc imports OK as boolean false. Anything else I tried - YES, NO, TRUE, FALSE, 1, 0 - all crashed the import to base with error message "incorrect type for setstring".
Moving boolean data from Base back into Calc shows values as TRUE or FALSE, so it looks like that is what the Base import is expecting. This works correctly for the HSQLDB engine but not for Firebird Embedded.
The bug has already been reported, so all you need to do is wait for it to be fixed.
In the meantime, it's possible to write a Calc macro to read the values from the spreadsheet and run a SQL UPDATE statement to get the correct values into Base. My answer here has some code to get started.
However, there is an easier way. Create a temporary Base file that uses HSQLDB and import the data into it from Calc. Then, close Calc and open both the Firebird Embedded and the HSQLDB Base files. Drag the table from the HSQLDB Base window into the other window, which imports seamlessly.