Importing Booleans (e.g. from Calc)

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER
Post Reply
dave2007
Posts: 10
Joined: Wed Apr 16, 2008 5:08 pm

Importing Booleans (e.g. from Calc)

Post by dave2007 »

I am trying to import a Calc worksheet to a table in Base. All goes well, but one the fields which is currently defined in Base as a boolean accepting Yes/No/undefined (yes, 3 values) interprets my spreadsheet column the following way:
  1. empty (whitespace) - undefined (this is OK and acceptable)
  2. Y - no checked ('No')
I would like Base to import empty/whitespace worksheet cells as 'No' (unchecked) and the other cells as checked. How do I accomplish that?

I mean, I thought that any non-whitespace would be interpreted by the import engine as 'Yes' (checked) but apparently it seems to work the opposite way. What do I need to put in my spreadsheet cell in order for it to be interpreted as 'No'?

In other words, would '0' and '1' work? Some other values?

Thanks,
Dave
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing Booleans (e.g. from Calc)

Post by Villeroy »

Set the default value of the column in the target column to 0 or import a calculated spreadsheet column instead of the acual one. For instance =X2 should return zero for all empty cells. Then import that column instead of X.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dave2007
Posts: 10
Joined: Wed Apr 16, 2008 5:08 pm

Re: Importing Booleans (e.g. from Calc)

Post by dave2007 »

Villeroy wrote:Set the default value of the column in the target column to 0 or import a calculated spreadsheet column instead of the acual one. For instance =X2 should return zero for all empty cells. Then import that column instead of X.
Thanks, Villeroy. Setting the default value of the column is simple and I will shortly try that. But in regard to "import a calculated sheet" - how do I do that? In the import Wizard (in Base) there is no option for importing calculated sheet - have I misunderstood?

Thanks,
Dave
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing Booleans (e.g. from Calc)

Post by Villeroy »

I mean a calculated column. Let me describe a similar, more common issue for later reference. For instance, you may notice that the database converts numbers to dates differently than Calc does. Dates are two days off due to different zero-days (ISO-SQL 1900-01-01, Calc 1899-12-30). There are at least 3 ways to adjust:
1. Copy a number "2" into the clipboard and paste-special over the dates with option "Substract".
2. Create an additional column with dates adjusted by 2 days: =A2-2
3. Create an additional column with dates as ISO-strings: =TEXT(A2;"YYYY-MM-DD")
When using option 2 or 3 you skip the original column in the import wizard and import the adjusted ones.
If you notice wrong dates after import, there is no easy way to get a reliable UPDATE query with the limited toolset of the integrated database engine hsqldb.

In your case a simple reference to each cell of the boolean column returns zero for blank cells. All non-zero numbers will be imported as True into a boolean field. All zero numbers will import as False. In any case you should take care of common rounding errors in spreadsheets.
 Edit: Aaaargh, they fixed the minus-2-days-date-offset (in 2.4?). Numeric dates import correctly from Calc, but now ISO-strings are +2 days off. 
Last edited by Villeroy on Wed Apr 16, 2008 8:40 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dave2007
Posts: 10
Joined: Wed Apr 16, 2008 5:08 pm

Re: Importing Booleans (e.g. from Calc)

Post by dave2007 »

Villeroy wrote:Set the default value of the column in the target column to 0
OK, I just tried that and it doesn't work. :(
Here is what I did: I set the default value of the column in the target column to 'No' (OOo Base lets me set it to either 'Yes' or 'No' - no '0' or '1'). Using Find/Replace I changed all values in the spreadsheet column from 'Y' to 'Yes' (empty cells remained empty i.e. only whitespace). I then imported it.

The result was again that all empty cells translated to <none> (grey check mark) and all cells containing 'Yes' translated to 'No' (empty check mark).

What am I (or Base) doing wrong?

Thanks,
Dave
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing Booleans (e.g. from Calc)

Post by Villeroy »

dave2007 wrote:
Villeroy wrote:Set the default value of the column in the target column to 0
OK, I just tried that and it doesn't work. :(
Here is what I did: I set the default value of the column in the target column to 'No' (OOo Base lets me set it to either 'Yes' or 'No' - no '0' or '1'). Using Find/Replace I changed all values in the spreadsheet column from 'Y' to 'Yes' (empty cells remained empty i.e. only whitespace). I then imported it.

The result was again that all empty cells translated to <none> (grey check mark) and all cells containing 'Yes' translated to 'No' (empty check mark).

What am I (or Base) doing wrong?

Thanks,
Dave
After setting the field's default value in the GUI you have to save the table definition (click some other control and save before closing the table's design view). Test manually if a new record actually sets the default value. If not, save the database and reload.
Now there should be no need to change anything on the spreadsheet side as far as numbers are mapped to your boolean field. Since you decided to represent booleans as "Y"/"N" or "Yes"/"No" respectively, the import must fail. Import fails in most cases where text values are mapped to non-text database fields (however, ISO-date strings maps fine to date fields).
Numbers work regardless of their number format. Zero (formatted as FALSE or not) becomes FALSE ("No"), non-zero (formatted as TRUE or not) becomes TRUE ("Yes").
Add another column with a header in first cell and =IF(A2="Yes";1;0) and import that. [A2 is supposed to be the first "Yes"/"No"]

I wish this database GUI would not introduce those misleading text representations "Yes", "No", "Empty", german "Ja", "Nein", "Leer"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply