Ask Your Question
1

Copying boolean states?

asked 2021-02-25 03:37:41 +0200

Uncle Robin gravatar image

updated 2021-02-25 03:38:32 +0200

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2021-02-25 05:04:31 +0200

Ratslinger gravatar image

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:

image description

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.

edit flag offensive delete link more

Comments

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

Uncle Robin gravatar imageUncle Robin ( 2021-02-25 08:10:29 +0200 )edit

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 ...(plus)

Uncle Robin gravatar imageUncle Robin ( 2021-02-25 16:51:37 +0200 )edit

@Uncle Robin

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.

Ratslinger gravatar imageRatslinger ( 2021-02-25 20:17:30 +0200 )edit

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.

Uncle Robin gravatar imageUncle Robin ( 2021-02-25 21:53:09 +0200 )edit

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?

Uncle Robin gravatar imageUncle Robin ( 2021-02-25 23:59:13 +0200 )edit

@Uncle Robin

What is needed is the SQL statement you used to create Table4.

Ratslinger gravatar imageRatslinger ( 2021-02-26 00:15:19 +0200 )edit

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.

Uncle Robin gravatar imageUncle Robin ( 2021-02-26 00:34:10 +0200 )edit

@Uncle Robin

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 ...
(more)
Ratslinger gravatar imageRatslinger ( 2021-02-26 00:37:19 +0200 )edit

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

Uncle Robin gravatar imageUncle Robin ( 2021-02-26 01:30:53 +0200 )edit

@Uncle Robin

Sorry you went through all that. Please see my previous comment.

Ratslinger gravatar imageRatslinger ( 2021-02-26 01:38:21 +0200 )edit
1

answered 2021-02-25 08:10:35 +0200

Wanderer gravatar image

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

edit flag offensive delete link more

Comments

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.

Uncle Robin gravatar imageUncle Robin ( 2021-02-25 12:58:30 +0200 )edit

@Uncle Robin

Since you don't have/want NULL values the statement would be:

=IF(H2="Yes",1,0)

This is based upon your image provided.

@Wanderer,

This is a valid solution. However, based upon having over 12K rows I believe SQL may be a better direction.

Ratslinger gravatar imageRatslinger ( 2021-02-25 20:30:00 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-02-25 03:37:41 +0200

Seen: 51 times

Last updated: Feb 25