LibreOffice Base (03) Import Data from a Calc File

Fill the table created in video #2 with data from the calc file using cut and paste. Also shows how to cut and paste when fields are in different order.

The downloads to go with this series can be found here:

[thurein]

Thank you very much, Sir. Now I am learning Libreoffice Base with your videos. I really appreciate your videos. Thanks.

[ngsaraf]

Michael, thank you for putting in so much time and making these videos, covering the minutest aspects. I never thought I could ever get the hang of data bases, but I'm well on my way to making one viz. a customer management system, for my company.Thank you once again. Neeraj, Mumbai, India

[robitops]

It is an excellent tutorial,
It solve 4 problems, that I was looking for (Some says it is not possible)

1. Re-Order Table's Column name
2. Reset Auto_increment value
3. Duplicate Tables.
4. Copy from Calc to Base.

Thanks for sharing your knowledge with us freely. (Your are an awesome tutor too)

[BSaul]

I've been searching a week to find this simple answer. Thank you!

[MichaelsTechTutorials]

Thanks for the comment letting me know how much I am inspiring you to get back to learning Base!

Yes, there are some LibreOffice / Open Office forums that take specific questions – most of my questions have been answered by r4zoli, Villeroy, DACM, or eremmel if you type one of those names and base into a search-bar it will take you right to that forum.

A special thanks to them for sharing their expertise - much of this series could not be done without them answering other peoples questions

[angelikatorrie]

Just the information I was looking for! Thank you!

[ISRAELBRAVO]

Hey I'm from Brasil and I'm watching your videos. They are very good. Congratulations!!!

[robt]

Maybe worth mentioning that you cannot copy-paste direct from an .xls file, you need to copy the .xls into Calc, then re-copy and paste into the Base table as described. Otherwise, very helpful.

[MichaelsTechTutorials]

Thanks! I hope my video tutorials can help out your readers as much as they helped you out.

[juancarlosrojas]

Excellent work. Thank you for this tutorial.

[dmftradutor]

Hi Michael! Thanks for your videos. I'm learning it on LibreOffice Base 5.3 and it seems that copying/paste bold doesn't work anymore. Also, I was eager to know how to reset a table and restart from zero... It's a weird thing Base doesn't have a "table reset" button... But your working-around's great!

[Valdaquende]

I have an observation about the way that LibreOffice Base v. 5.4.3.2 behaves in the context of this lesson. One of the exercises is copying a row from a LibreOffice Calc sheet and appending it to the Employees table by pasting and assigning fields to the data being imported. In the demonstration, a new left-most column is added to the Calc sheet to represent the Employee ID field so that the number and sequence of the fields match. Then a row (or rows) is (are) copied to the clipboard and then pasted to the Employee table. It works in the video. But not with v.5.4.3.2 ...

Using v. 5.4.3.2, the newly-created ID field on the Calc sheet does NOT show in Base's 'Assign Columns' window UNLESS there is something in that field for at least one of the records involved; it can be a space or any other normal character but it cannot be empty. If there is a only a space, the 'Assign columns' window will display the field as 'Column' the same way it normally does for any other empty column during an import of this sort.

If, on the other hand, the field contains visible characters, those characters are displayed along with the contents of the other fields just as it normally does. I have demonstrated this with v. 5.4.3.2 on both a PC and a Mac; the behaviour is exactly the same. And since the field does not appear in the 'Assign Columns' window, you cannot compensate for it and all the fields will be imported one-field-off, so to speak.

If there is no data in the initial field of the record(s) you are attempting to import and you experience this problem, just put a space into the problem field of at least one of the records you are trying to import; that should solve it.

Like the other commenters here, I want to express my gratitude for the excellent job you've done.

[MdMozammelHossain]

you are super cool guy ! thanks for tutorial !

[dw]

I attempted this numerous times without success. The system gives the error "Attempt to insert Null value "ID" into non-nullable column. Apparently even though I DE-selected the ID column - the system still thinks I want to populate it with null data. I'll try and remove the ID field and see if that helps.

Yes - removing the ID key field and copying only the data did work. The table is now populated with data. Wonderful! So nice to not have to enter all of that data by hand! :-]

[borg]

Any reason you know of if you right click paste on a table you created that the paste option is grayed out?

[ernestobosch]

Fantastic!! I struggled a lot to make a data base (I'm not a computer
guy, at all) untill I found your channel.

I love your pedagogy. In the past I tried some books but all of them or
taught data base design from a rather teoretical view without specific
software connection or explained how to use a software assuming one is
already profiency in data base design.

With your material I've been introducing in both subjects
simultaneously.

I'm currently using LibreOffice 6; any chance you update the material to
cover the new LO capabilities. Please.

PD: sorry for my english, I'm an spanish speaker so...

[maciejkulczycki]

Very nice. But what if I have the following message:
firebird_sdbc error:
*validation error for column "Table1"."ID", value "*** null ***"
caused by
'isc_dsql_execute'

Continue anyway?


And the data base freezes for ages.

[cnstantin]

8:28 is the data column the only thing that's on "far right"? :D

[MatthewPolack]

Hi .thanks for the video....just a note that I've also discovered you can no longer 'Paste' in the data! I right click and choose Copy in Calc..then go to Base..Right click..and the paste menu is greyed out. If anyone knows if it is possible to get spreadsheet data from Calc into Base...would love to know the secret! Thanks!

[robt]

Hi Michael, winter's back and so therefore am I! Thank you for maintaining all these LO tutorials online.
In creating tables in Access there is an option to limit fields to unique values whereas with Base it seems that this is only possible for the primary key field. It's a good way of avoiding data duplication. Have you addressed this anywhere?