How can I paste the definition only of a Calc sheet into a new table in Base? (using the pasting feature)

[LobaLuna]

This is a double question and a report of a bug, maybe, I do not exactly about the latter one.

I have a table in Calc that I copy (with labels in first column). All data in each column is coherent (no text as dates, nor text as numbers, etc.). Just copy the cells that I am interested in. Then open my ODB file in Base and go to Tables panel and paste it. The Tables Wizard opens.

In the first step, I choose Definition (not Data and Definition), and follow the next steps. I also choose to add a new primary key. All remaining steps with no frills. When I hit the Create button, nothing happens. No new table is added.

I assume the Definition option will create the structure of a table with such and such definitions. Maybe I am wrong. I skimmed thru the LibOBase 6.4 Guide and I couldn’t find anything about creating tables from Calc spreadsheets using the pasting feature.

Instead, when I do the same procedure, except that I choose Data and Definition in step 1 of Tables Wizard, everything is OK. I get my new table. Of curse I can erase all the pasted records and I get my definition… but that not the point. I believe this is not the way to go.

1. My first question is how can I paste the definition only of a Calc sheet into a new table in Base (using the
   pasting feature), so I can enter data later?
2. My second question is what is the Definition option (in step 1 of Tables Wizard) intended for?

If this is an irregular/buggy behavior, I’ll post a bug report, of course.

I have tested this behavior in LibO 6.2 and 6.4 in Lubuntu 20.04.

[frofa]

Hello…

I can confirm the behaviour you describe using a blank Firebird Base file and a Calc Sheet. By selecting ONLY the header cells in the Calc sheet (not including the ID column), and letting the import wizard add an ID PK, the table is created as expected, using the settings you describe. NOTE: You are correct. It is necessary to use the Definition and data and first line as column names options for this step. No table is created if the Definition only option is selected.

HOWEVER, THERE ARE OTHER PROBLEMS:

1. the ID is NOT set as auto-value, and when I try to do that through the EDIT interface, I keep getting the message

   The column “ID” could not be changed. Should the column instead be deleted and the new format appended?

but, if I say ‘YES’ to the message above, I get an error message…

*ALTER TABLE Table2 failed
*SQL error code = -607 *Invalid command 

2. All newly-created columns of the table appear as VARCHAR (255) regardless of the type of data the columns have in the Cal sheet. I can change them to the correct data-type in the table edit window, but the changes only appear AFTER saving, closing and re-opening the FB file.

To setup tables, I have found it a lot easier to just run some SQL as per THIS post…

How to create an auto-increment ID column in a Firebird embedded database table? Errors encountered

These steps what I have tried so far without success. (Note: Firebird embedded does not seem to require quotes around names like HSQLDB but also seems inconsistent?) What am I doing wrong? *1 Create a table in the a LibreOffice Firebird embedded database called “phonecalls” … (this works) CREATE TABLE "phonebook" ( "id_pn" INTEGER NOT NULL PRIMARY KEY, "phone_number" VARCHAR(16), "name" VARCHAR(128), "date" DATE, "note" VARCHAR(256), "flag" VARCHAR(2) ); *2 Create auto-increment ID generator…

…and setup the auto-value INTEGER PK as per code sample provided in Drew Jensen’s post. You should then be able to APPEND your data by pasting from the Calc Sheet. NOTE: your column names in the Base table much exactly match the Calc sheet column header names.

[LobaLuna]

Hi, @frofa

Thank you for your answer! I didn’t tested with a Firebird engine file, but HSQLDB one. So now the bad behavior duplicates ;D

Although your solution is quite interesting and useful, I am not sure to use it because what I am doing is updating an old tutorial for OOo Base to LibO (the original author is TheFrugalComputerGuy) and this tutorial is intended for novice people. I do not imagine new Base people confortably writing SQL code…

I will keep the solution for myself under my sleeve, thou!

Concerning the Definition option of the wizard, I shall report there is as a bug there.