user guide states quite clearly that if one imports a Calc sheet into Base as a Table, it is imported ‘Read Only’.
What is simplest way to convert it to Normal, i.e. Editable and NOT Read Only
You (or the user guide) confuse import with a spreadsheet connection.
When you call menu:File>New>Database… and choose “Spreadsheet” as database type, you link a Base document to a spreadsheet which is a way to present spreadsheet data as if the data were stored in a database. This is useful (and good enough) for mail merge or for simple sort/filter queries that can be linked back into a spreadsheet.
If you want to import (copy rather than link) sheet data into a database, you need to a true database and consistent sheet data that follow the rules of your target database.
Villeroy & fpy
Thanks for the replies.
Villeroy, I have tried it with both an embedded HSQLDB and a MySQL connection, by dragging and dropping the Calc Sheet onto Base ‘Table’ tab, to add it as a new table. The end result was the same, a ‘linked’ table that is read only!
fpy, your image isn’t complete, and the steps shown, stop at append data, which I have tried to do in the past, but was unsuccessful. If you would be so kind as expand your image to show ALL your steps, perhaps I unknowingly missed something.
NB I set the column headings of the speadsheet to accurately reflect the field names in a newly created table, before trying to append it to the existing Table, without success.
was just a glimpse of typical AI; suggesting it may help you to clarify the concept (copy vs link)
I never searched for simple…
- So I create the necessary (empty) table first in base, manually setting all types (fitting the data in the spreadsheet).
- Now mark the whole spreadsheet and copy to clipboard
- In Base use the context menu of the table (don’t open the table) and select paste.
Now you should see a wizard to help you appending you data to the table. If spreadsheet and database have columns in the same order you don’t need to change anything after the first page. Just continue until import is done.
.
This creates a copy of your data in a database table. It is editable, if you have a primary key in one column. I usually create the needed key in Calc myself or leave the field empty, if I ave defined an autoincrement for the key.
1 Like
When you copy a cell range into a true, editable database, you get a copy with no primary key nor any other keys, possibly inadequate column types.
I prefer importing data into existing tables. The import fails, if database rules are violated, which is a good thing. Then you have to fix the spreadsheet data until they fit.
If you are more familiar with databases than with spreadsheets, you may copy&paste into new table and treat this as a temporary table from where you import consistent data by means of SQL statements.
create_table.ots (41.3 KB) is a spreadsheet template trying to analyze spreadsheet data, helping to fix them and finally generating a CREATE TABLE statement with a primary key.
Paste-special (values only) your data with column labels to A1 of the first sheet. Use the built-in cell styles for ISO date-times. Abstain from further formatting.
On the second sheet, push a macro button and work through the colored cells.
The template is somewhat unfinished and far from perfect. However, last time I tried, I got valid record sets and new tables for HSQL, Firebird and MariaDB with all listed simple types representing text, numbers, date-times.
Villeroy,
Thanks for template, a little confusing at first look, but I got there in the end, and successfully created the Table with the resulting SQL statement.
However that wasn’t my proiblem, as I had aready created a similar table.
The problem is how to get the data from the spreadsheet into the Table,(See below)
wanderer,I had already tried the copy and paste method to get the data into an existing table, but my problem is that Paste doesn’t appear in the context menu, even though I know that the spreadsheet is in the clipboard (i.e. I can paste it into another spreadsheet, just not into a Table!)
Copy the cell range.
Select the icon of the matching table. Do NOT open the target table!
Paste to the selected table icon.
A dialog pops up with the name of the selected table in a text box and option “append data” pre-selected.
My spreadsheet template generates the columns in correct order, with any additional primary key as last column, so you just confirm the dialog without any adjustments.
If the order of copied columns differs from the target table’s order of columns, you can map the right columns to each other in a second step of that dialog.
Villeroy,
As I said before, Paste is not available, even when I highlight the new Table. That’s why I’m struggling! 
Any idea why that should be?
I have no idea. Can you open the table and enter records manually?
Villeroy,
No I can’t add rows.
I just created a simple table in Design View, and I can add rows to that, but the one created by dragging and dropping the sheet, and the ones created via sql (yours and mine) are all read-only!
However, none will accept a Paste from the spreadsheet copy!
Furthermore, if I right-click any table and Copy from context menu, Paste option on context menu still not available.
Something weird is going on
I don’t understand what’s going on.
- Tell us the exact content of your status bar or what you see in Edit>Database>Properties…
- Operating system and LibreOffice version.
- The location where your database document is stored. Database documents should always be stored on the local hard drive.
- Can you edit any other tables, e.g. a newly created dummy table with primary key?
Please upload the .odb here. You may need to add another extension like .odb.odt as some types are not in the allowed list.
villeroy,
The table was created fine AND I was able to drop the sheet and append the data. Furthermore the Copy/Paste functions are both available on the context menu.
What’s the secret,what have you done different to me?
Actually this sounds consistent. If the table is read-only a paste will fail, so it is a good idea not to show “paste”
.
My best guess, why your tables are not writable is lack of a primary key. Check your table, if you assigned a primary key. Or again: Upload the sql-created table here for inspection.
Wanderer,
Thank you, whilst experimenting, I was beginning to come to the same conclusion
Once the table has been created without a primary key, one can’t then add a new field as a primary key, because table is Read-only.
Sadly there is no-mention of this in the help, only that dragging a spreadsheet into Base creates a read-only table, but doesn’t explain why!
!Villeroy and Wanderer I thank you, and think my problem has been solved. I’ll leave it for now, but may come back if it other issues pop up
Further to above answer, I just successfully dropped a spreadsheet into Base as a new table.
Whilst doing so, I read the warning that apears about having a primary key carefully, and it does state that without creating a primary key, the created table will be read-only.
If only I had read that properly the first time - duh
Still begs the question why, if it is so critical, it isn’t emphasied more in the help