Base: Importing a Calc sheet

English
#2

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.

#3

image
image601×520 71.5 KB

#4

Which status bar is like yours?

Variuos_Status_Bars
Variuos_Status_Bars827×539 94.7 KB

#5

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.

#6

was just a glimpse of typical AI; suggesting it may help you to clarify the concept (copy vs link)

#7

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
How can I copy/import a CALC file into BASE?
#8

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.

#9

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!)

#10

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.

#11

Villeroy,
As I said before, Paste is not available, even when I highlight the new Table. That’s why I’m struggling! :grinning:

Any idea why that should be?

#12

I have no idea. Can you open the table and enter records manually?

#13

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

#14

I don’t understand what’s going on.

  1. Tell us the exact content of your status bar or what you see in Edit>Database>Properties…
  2. Operating system and LibreOffice version.
  3. The location where your database document is stored. Database documents should always be stored on the local hard drive.
  4. Can you edit any other tables, e.g. a newly created dummy table with primary key?
#15

Please upload the .odb here. You may need to add another extension like .odb.odt as some types are not in the allowed list.

#16

ask120281.ods (59.9 KB)
ask120281.odb (3.0 KB)
Can you copy this?

#17

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? :grinning:

#18

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.

#19

Wanderer,
Thank you, whilst experimenting, I was beginning to come to the same conclusion :grinning:
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

#20

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

#21

I have no idea what you do. This is what I did:

  1. I imported an arbitrary record set from a test database into my spreadsheet template. Since this record set comes from a database, I do not have to bother about consistency issues.
  2. Formatted the date column with cell style “ISO Date”.
  3. I stored the spreadsheet in a trusted directory, so the little macro is allowed to run.
  4. I ran the macro and filled out the “form” on the second sheet for database type “HSQL”, including an additional primary key (3. option on my “form”, cell A9).
  5. Created a new database document with an embedded HSQLDB (any existing database with external or embedded HSQLDB should work as well).
  6. Copied the CREATE TABLE statement from the sheet.
  7. Ran it in the SQL dialog. Got a success message.
  8. menu:View>Refresh tables.
  9. Copied the record set from sheet.
  10. Pasted to the table icon and confirm the dialog.

My record set has 4 columns, the table has an additional auto-ID at position 5. This way the first 4 columns are filled with sheet data without touching the auto-ID at the 5th position.

If you are sure that you want to use an existing field as primary key, leave option 3 in my “form” blank and mark the column in row 27. If the cell validation does not allow this, the column data are not unique, which is a precondition for any valid primary key.

P.S. It is possible to paste data to an existing table that has no primary key. The result is a read-only record set.
The paste command is disabled (grayed out) only if the connected database is read-only. I can think of several reasons, why this may be the case.

  • It’s a pseudo-database, e.g. spreadsheet.
  • An embedded database is extracted to a temporary folder with no write access.
  • User/Group priviledges imposed to the logged in database user by the database backend (DB servers such as MySQL).

A read-only database document can edit the connected database if that database it editable at all.
CAUTION: I can edit table contents of an embedded Firebird DB, even though the document is read-only. Writing back the embedded database to the embedding document fails silently. No changes are saved. Base should lock an embedded database for writing if the embedding document is not writable (like it does with embedded HSQL).
P.P.S. All this happens in a directory with write access on a local hard disk.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.