Add/move big data from Calc to Base

[TotallyLost]

Have we any easy way to get from Calc table cells to Base table records?

Drag and drop is not possible, but years ago using old MS Works it was possible. I mean this: When I have painted Cols A and B from row 1 to example 30 and then drag it with cling to cell A1 from Calc table to drop to Base table to topmost record’s first free column (next from key column), then every cells will arrive to their own place on Base table fields. It doesn’t work with LO Base.

So have we any easy way to do this without drag and drop each cell contents one by one to Base table fields? Thats important when there is waiting thousands rows in Calc table.

Thank you!

[fck]

Migrating from Access to LibreOffice for me is a big cultural shock. The first impressing is that Base look so similar that it should easy, but once you start using it, a steep and frustrating learning path seems to await you. Even doing simple things, like changing a table’s name is difficult exercise. You can only add a new table field at the end of the list! Even a minor change, such as extending a string field size, or field re-ordering is a big undertaking. All these things are a breeze in Access. To use cut and paste to populate a table is mind boggling. In Access you point to the spreadsheet, pick the page and it is done. Yes, you can import files in Base, but only at database level and not at table level. What is the use of a single table database? Maybe I am struggling because I am still a new-bee, but the ease that I managed Access vs the struggle with LibreOffice base is for me a huge disappointment.

[Ratslinger]

Hello,

Not certain why you are having a problem but I have done this dozens of times with & without column titles.

Just performed another test. From Calc, 27 rows (row 1 headers) and 25 columns to Base creating new table based upon Calc fields. In other words, no predefined table. The went back & selected another 27 rows & 25 columns and appended that to the just created table. No problems.

The first time I used drag & drop. The second, copy & paste. Both work and have ever since I can remember.

EDIT:

Sorry, just re-read your question. It may be you are dropping the data into the wrong place. You drop/paste it onto the actual table name on the main Base screen, not on the view of the table data. Follow the dialog from there. Also you are possibly going to have a problem if copying to a table already defined with keys and those keys are not included with the copy.

SECOND EDIT:

Just want to help in case you are not aware. It appears you are new to Base. Be aware the default embedded DB can corrupt data especially with large amounts of data or even a few embedded images. To avoid this problem, start with a split DB (has upgraded HSQLDB). Instructions found on this post - click here.

THIRD EDIT:

Steps to copy -

Select data in spreadsheet. In this example the first row will be used for the field names:

Once selected, right mouse click & select Copy. Next to Base. In Tables (where list of tables occurs) right mouse click & select Paste:

This will bring up the dialog:

Here table name to be created is at the top (change if wanted), Definition and Data is selected, and because of using Column headers for first row, make sure Use first line as column names is selected.

At this point you can select Create button or Next for further modification to the copying. In this sample, Create was chosen and another dialog appears asking if Key to be added:

Yes was selected and here is the resulting table:

[TotallyLost]

I have a capture from this situation:
https://photos.google.com/album/AF1QipPivOubwtkFZ7K-rrK-h6Nx4hFN9Fzm5S5KJkKz

And I tried also without keys, but something missing.
When I drag and drop cells from Calc, whole contains from all cells goes to one field of Base table.

EDIT: Yes I’m new with LibreOffice and in this forum. I don’t even know should I vote answers and how or not. But need to learn between link you offered. Thank you.

[Ratslinger]

First, voting is up to you. See Getting Started for more info. Next, can’t see your screen capture - getting error. Should actually post in question but posting as WIKI (not recommended) may not allow & also need at least 3 Karma. Instead, I will post addendum to my answer with screen shots. Finally, can’t stress enough - USE SPLIT DB!

[Ratslinger]

I also want to mention, there are a couple of drawbacks to using a split DB. Most prominent is that you cannot edit a table using the GUI; it must now be done using SQL ALTER statements. You can, however, add or delete fields using the GUI. When copying tables containing an auto increment field from another DB, special handling must be done for the auto increment field to be correctly copied. These are only two I can think of off hand. To most, this is easier to deal with than losing data.

[TotallyLost]

Yes. This working! This successful have the second time around when I tried. The first time it came to an end that Base reported “JRE is defective”. I killed the process and again - then it succeeded right away.

I have already previously the same problem today with this PC that Base says “JRE is defective”. What can it be that sometimes it is bullied and sometimes not?

[Ratslinger]

Very glad it now works. As for the JRE, I use 64-bit LO on Linux using OpenJDK and never have had a problem. I’ve run across more than just a few posted problems when it comes to Windows (especially 10) or MAC OSX. Each one seems to have its’ own variation.

[Ratslinger]

BTW - By accepting the answer, you now have enough Karma to post screen shots in your question. Please do not post as WIKI.