I’m trying to import a Calc file as a table, and keep the cell format intact, but Base 7.1.0.3 insists on making all fields VARCHAR. If I create a new table and mimic the cell format in defining the field type, I have a problem with the Boolean type. In my Calc file, I have 250 rows and a “YES” in all of them in one boolean column ( around 5-7% will ultimately be “No”), and 70/30 mix of “NO” and “YES” in another. If I define the Field Type of my table in Base as Boolean, I get an uneditable field length of 1, and the “YES/NO” data does not get imported. This table is a test for my main table, of 12500 rows, so I would love to be able to import the data with the relevant Boolean states if possible.
I would be comfortable with creating the table in SQL if this is a limitation of the Base GUI, and I don’t care that much if it’s a checkbox (as at present) or a list of "yes"es and "No"s, since I know that’s an appearance issue which can be addressed relatively easily. I just don’t want to have to manually re-enter all the current data.
Hello,
Sorry changed my mind on a second read. Basing this on YES and NO in columns.
Imported into Base as Varchar. Then with a second table definition set the same except the Yes/No field set as Boolean, used SQL to copy:
Insert Into "Bool4b" SELECT "id", "name", Case When "b0" = 'YES' Then 1 Else 0 END FROM "Bool4"
Result:
Bool4 was original paste and Bool4b is the SQL inserted data. Tested with HSQLDB embedded & LO v7.0.4.2 on Ubuntu 20.04. Other databases will be similar.
If this answers your question please tick the 
(upper left area of answer). It helps others to know there was an accepted answer.
Thank you for your very prompt response. I won’t be have time to try it out until tomorrow, but several careful re-reads of your answer are making me feel confident that it will be exactly what I’m looking for. When I have confirmed this by testing tomorrow, I’ll be sure to mark the question as answered - THANK YOU
I have marked this as “answered” because I was eventually able to replicate your success, thank you! However, the target table actually has TWO boolean fields, and I could only do the “import/copy” procedure outlined above successfully if I used a table with only the final column boolean. When I tried to follow the procedure with the original table (in which the first boolean field is followed by a date field then another boolean, I got a series of SQL errors which I screencapped. I tried to be METICULOUS in copying the syntax, but trying as 2 separate commands bought up a “column count does not match” error, and the other attempts generated “unexpected token” errors, as shown in the screencaps HERE. If you could explain where I went wrong with this attempt to build on the success you helped me achieve with a truncated version of my target table, I would be very grateful.
From what I see you did not create a correct Case statement for one selection. See → The SQL CASE Statement
In your image lower right you have (portion of SQL):
Case When "KEN" = 'YES' Then 1 Else 0 "Date", Case When "ACTIVE" = 'YES' Then 1 Else 0 END
In the first Case statement there is no END. Probably should be:
Case When "KEN" = 'YES' Then 1 Else 0 End, "Date", Case When "ACTIVE" = 'YES' Then 1 Else 0 END
This is the cause of Unexpected token... you see.
Edit:
Based upon other answer and your comment there, it may be best to verify that your bool columns do in face only contain yes/no (seen you have presented different spelling of these) by doing a Select statement with a where clause. This may save you many headaches later.
THANK YOU! The problem was that missing END. Inserted that, all worked perfectly. SO grateful for your help, and feeling encouraged that my first attempt at playing with SQL came off so nearly right, despite being done at 4AM after a longish night shift.
A new wrinkle, sorry! When I added the missing END - the SQL command window said “command executed successfully”, and the output window said “200 records updated”. But when I compare the 2 tables, the boolean checkboxes are all empty. Following your suggestion above, I ran a SELECT WHERE on each of the two columns in Table3 that where meant to be boolean. The second of them has some “No” entries, as shown in the screenshot HERE . Obviously, I am still doing something wrong, do you have any suggestion what my mistake might be this time?
Whoops! I did not create Table4 in SQL, I used “Create Table in Design View” from the GUI. I did notice that when I saved Table4 after doing so, the length of each of the Boolean fields was “1”, but after running the import, and checking the fields in Edit mode, the length had changed to “0”. I will try writing the table instead and see what I can learn.
Sorry - my mistake! No need to create table in SQL. What I am looking for is the SQL INSERT statement you used to create the data in Table4. I suspect your Yes & No is incorrect!
Also, most SQL one executes are Select statements or Queries. These are best run in the Query section as they give better visual feedback. Tools->SQL is typically needed for SQL such as Insert or Create commands. This is a general rule as there are more complications with this.
Now when I had stated to run a Select statement with a Where clause, it was to be done in the Query section but differently than what you have. Using Table3 (the table created from the Calc data), to select anything which did not have Yes/No in the column. This would tell you of any bad entries. SQL:
Select * From "Table3" Where ("Ken" <> 'Yes' And "Ken" <> 'No') Or ("Active" <> 'Yes' And "Active" <> 'No')
This would produce a list of accounts you could then correct.
I tried again, this time creating the table in SQL
DROP TABLE Table4 IF EXISTS;
CREATE TABLE Table4
( ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY ,
FOO VARCHAR(255),
BAR VARCHAR(255),
FOOBAR1 VARCHAR(255),
LOREM VARCHAR(255),
IPSUM VARCHAR(255),
TOW VARCHAR(255),
KEN BOOLEAN NOT NULL ,
DATE DATE ,
ACTIVE BOOLEAN NOT NULL
);
And the table was created successfully. When I ran the copy/import, the same result as before “command executed successfully” “200 records updated”, 200 empty boolean checkboxes
“This would produce a list of accounts you could then correct.” Eureka! You were absolutely right - every single cell that LOOKED like it contained “Yes” or “No” does not contain either. I take it this means the problem is indeed with the source Calc file, so will start from scratch by making sure that the relevant source columns actually ARE Boolean, not merely appear to be - as they do appear to be in this screenshot
“Sorry you went through all that. Please see my previous comment.”
Apology TOTALLY not necessary! I love learning new languages, and since I don’t get much chance to improve my Italian or Hindi at the moment, SQL is another fun new language to learn. I’ve never been shy about making mistakes, they teach so much.
No. Your Table3 does show Yes in columns which seems good. PLEASE provide the Insert statement you used. That is THE key!
Here is the command as pasted from Notepad++ into the SQL command window:
Insert Into "TABLE4" SELECT "ID", "FOO", "BAR", "FOOBAR1", "LOREM", "IPSUM", "TOW", Case When "KEN" = 'YES' Then 1 Else 0 END, "DATE", Case When "ACTIVE" = 'YES' Then 1 Else 0 END FROM "Table3"`This is where the problem lies. Please follow along. In your original question you state as to having “YES” and/or “NO” in the each of the two boolean columns. That is OK and I presented my tested answer upon that information. Now when you presented other screen shots in comments above & for other answer you presented a different value in the Calc cells. I alluded to this in my comment above. It appears you cells contain “Yes” and “No” and NOT “YES” and/or “NO” as stated in the question. Therefore, your SQL should be:
Insert Into "TABLE4" SELECT "ID", "FOO", "BAR", "FOOBAR1", "LOREM", "IPSUM", "TOW", Case When "KEN" = 'Yes' Then 1 Else 0 END, "DATE", Case When "ACTIVE" = 'Yes' Then 1 Else 0 END FROM "Table3"
By having “YES” instead of the correct spelling, “Yes” each case statement never had a true condition and therefore all were set to 0 or false!
Hope you see how important accurate information is when asking questions and things such as SQL.
Thank you for that very clear and concise confirmation of a conclusion I had come to - that the problem was “The Case of the Wrong Case”. A process of elimination had led me to think that it must have been the cause of the problem, and was about to try fixing it myself. Thanks to your generous assistance I know I was on the right track.
Hello,
my answer would be similiar, but handling the data in Calc:
IF(cell='YES';1;IF(cell='NO';0;""))
With this you convert the value in a new column an copy the results over the old column (copy only values/numbers, not formula). Then remove extra column, or leave this out, when data is copied to Base.
The extra “” should give NULL if the original Data were empty.
PS: replace “cell” with the appropiate column
Thanks, I might try this first. There are no nulls in the Calc source file, so all I need is
IF(cell=‘YES’;1;IF(cell=‘NO’;0;)) - correct?
I guess I shouldn’t try this at 01:20. I pasted the IF statement directly into anew cell, replacing “cell” with the target reference, as in this screenshot
IF problem
I get the #NAME? error, and can’t figure out what I’m doing wrong.