LibreOffice Base (03) Import Data from a Calc File

[@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

[@thurein3097]

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

[@robitops1547]

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)

[@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

[@BSaul]

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

[@angelikatorrie958]

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

[@ISRAELBRAVO]

And you explain very well....

[@robt2151]

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.

[@borg9355]

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

[@MichaelsTechTutorials]

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

[@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!

[@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!

[@sandikusnadi8740]

Got the same problem as well.

[@juancarlosrojas5641]

Excellent work. Thank you for this tutorial.

[@maciejkulczycki3882]

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.

[@vizcarraorozco4740]

Hey Guys.... This is what I get when I click create "Warning! Value too long in Statement [INSERT INTO "Clientes" (Last Name", "First Name", "Gender", "Start Date", "End Date", "Phone Number", "Address", "City", "State", "Zip", "Department", "Group") VALUES (?,?,?,?,?,?,?,?,?,?,?,?)] Continue anyway? No data from OpenOffice Calc gets copied to Database3, whether I click "Yes" or "No". Can someone help me please. Thank you.

[@dw6528]

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! :-]

[@chandanabasnayake]

Excellant work. Thank you

[@c0nstantin86]

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

[@joelgarciadacosta6215]

Very good! 👋👋👋👋👋

[@hdvm1961]

I've tried the same procedure at OpenOffice and it doesn't works .. why ? .. Is there something at LibreOffice that makes the difference ?? Just curiosity ..

[@MichaelsTechTutorials]

Daniel Villegas Yes, LibreOffice is different than Open Office. Although they are very similar, the people at LibreOffice have had many updates and bug fixes that are not in the Open Office package.

[@hdvm1961]

TheFrugalComputerGuy Thank you I appreciate very much your confirmations about my suspicions.

[@pgsmyczek]

sorry but how do you start the counter at 1?

[@KutWrite]

Yeah, me, too. Counting from 0 is what programmers do. He doesn't answer questions, though. :(

[@kathleenmckenna4004]

uncheck the box for 'top row is column header' , a record 0 will be added for the col header line, then delete that record

[@MdMozammelHossain]

you are super cool guy ! thanks for tutorial !

[@pewlivepie5006]

This doesn't seem practical for bigger data... is there another solution to copying/moving data?

[@robt2151]

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?

[@YvanHarvey]

Why do I get unexpected token when I try to copy my spreadsheet from calc?

[@TheKutta78]

This is what I get when I click create "Warning! Attempt to create null into a non-nullable column:column: Employee-ID table: Employees in statement [INSERT INTO "Employees" (Last Name", "First Name", "Gender", "Start Date", "End Date", "Phone Number", "Address", "City", "State", "Zip", "Department", "Group") VALUES (?,?,?,?,?,?,?,?,?,?,?,?)] Continue anyway? No data from OpenOffice Calc gets copied to Database3, whether I click "Yes" or "No". Can someone help me please. Thank you.

[@xyzt123456]

Same problem here when pasting; but then, you asked that question 3 years ago...

[@Professorkek]

@@xyzt123456 Here another 3 years later with the same problem..

[@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.

[@Valdaquende]

An edit to the above comment. I found ... AFTER posting the above, of course ... that the difference lies in how you select the rows you want to import. If you select the rows you desire by clicking their labels on the left ('9', '10', '11', etc.) the first column is, indeed, omitted from the import. If you drag to select the actual cells themselves (say, 'A9' through 'M11'), the import works as shown in the video. I was selecting the rows by the row labels and didn't realize that that would make a material difference but it appears that it does. This behaviour is identical on both Mac & PC.

[@ernestobosch5833]

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...

[@GoodCrusader]

that's easy. How to get a macro with load xls or odt file?

[@robertoluongo57]

When I try to change ID from non autonumbered to auto-populated, it gives me a mistake: the column can't be changed. Why?

[@micahspecial5927]

is there any sample for calc file

[@armandojrsolano]

why i don't have a paste button in my database??

[@terrymusgreave7451]

Good tutorial

[@devilgene7330]

Thanks a lot!

[@robertoluongo57]

The same happens when I change anything! I'm obliged to copy a table into another table in order to modify it!

[@JustinAquino]

Great Tutorial! Thanks Frugal Computer Guy! I've manage to get it to work a bit. I've been having some isses simply pasting the data. I keep getting java.lang.NumberFormatException and java.lang.IllegalArgumentException Experimenting, I manage to Paste but I choose: definition and data and create ID. From there edited the Fields but left Date a Varchar hoping I can figure out a way later on to fix it. It worked. Libreoffice 5.1.4.2 on Ubuntu 16.04.1

[@robertoluongo57]

I got it! I lacked 64 bit Java

[@danielroque1669]

Thank you!

[@Nandi1963]

¡Qué bien explica coño!

[@maynardg.krebbs4741]

In my opinion we need to start creating forms just after creating the first table and please stop using Calc that's just confusing. Create a database or create a spreadsheet, but not both. You would be surprised that many times people are using databases first A database should be called a datasheet and leave it alone until later. Saying spreadsheet then datasheet then spreadsheet is confusing. Start learning about databases as a flat file first. Get to know real well as a database as queries and reports, then start designing multiple tables and then relationships . Datasheets, first off start using crosstabs as a real datasheet. Spreadsheets as a database is a little confusing, keep it as a datasheet as that, a datasheet using crosstab format. It's a lot easier and simpler and then one can get more involved about spreadsheets, and datasheets. Most people are not experts, we are novices as they say in the KISS principle, keep it simple stupid. Using crosstabs simpler and easier. I first used databases in an old DOS program called " Nutshell", it was the best and easiest to use and learn on. Yes, I am an old fart. Oh, one more thing I cannot stand Microsoft Access, who did this was a bunch of morons!! Either Oracle ( too expensive) or Dbase its still available and it works. Many years ago there was a program in the late 1990's called "Personal Oracle" for $99.00 as a training program. As usual they stopped making them. One last thing design your form with last name, first name side by side, its easier and better.

[@Deontjie]

.

[@ernestobosch5833]

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...