bad real values in a table

[antonio valanzano]

I have imported an xls file in a spatialite DB and then I have created a new table

















After the INSERT INTO the situation is the following and as you can see there is a value 182,69,
which is not recognized as an error and it becomes the max.
I know that SQLITE has a special treatment of data types.

If you have thousands of data it could not be easy to discover such situation.

Is there a way to intercept this error ?

[a.fu...@lqt.it]

On Wed, 29 Nov 2017 00:40:09 -0800 (PST), antonio valanzano wrote:
> I have imported an xls file in a spatialite DB and then I have
> created a new table
>
> After the INSERT INTO the situation is the following and as you can
> see there is a value 182,69
>

Hi Antonio,

please notice: in SQL the decimal separator is always represented
by POINT (.), not by COMMA (,).

short summary: on all English speaking countries POINT is the
delimiter between the integer and the fractional part of a
decimal number, while COMMA is the optional thousands separator.
many countries speaking Latin languages adopts the opposite
notation; so we'll have:

UK, USA and so on:
1,500.25 or 1500.25

Italy, Spain and so on:
1.500,25 or 1500,25

so your '182,69' definitely is a TEXT STRING, and can never
be automatically converted into a FLOATING POINT DOUBLE
simply because it adopts the "wrong" decimal separator.

please check better your XLS spreadsheet; you'll almost
surely discover that the corresponding cell isn't of
the NUMERIC type but is more probably of the TEXT type.
this is not at all surprising, because it's a well
known issue affecting Excel and other spreadsheets.

> which is not recognized as an error and it becomes the max.
> I know that SQLITE has a special treatment of data types.
>
> If you have thousands of data it could not be easy to discover such
> situation.
>
> Is there a way to intercept this error ?
>

certainly yes.

SELECT *
FROM mytable
WHERE typeof(mycol) = 'text';

this first query will identify all rows containing an
unexpected value of the TEXT type (presumably, all the
ones presenting the wrong COMMA decimal separator).


SELECT *
FROM mytable
WHERE typeof(mycol) = 'text' AND instr(mycol, ',') > 0;

this second query is more specific; only the rows
effectively cointaining a COMMA will be selected.


SELECT *, replace(mycol, ',', '.')
FROM mytable
WHERE typeof(mycol) = 'text' AND instr(mycol, ',') > 0;

in this third (purely didactic) query we are calling
the replace() function so to change all COMMAs into
POINTs.


UPDATE mytable SET mycol = CastToDouble(replace(mycol, ',', '.'))
WHERE typeof(mycol) = 'text' AND instr(mycol, ',') > 0;

now we are finally ready to update our Table so to
correct any oddity; the CastToDouble() functions will
transform all values from TEXT to DOUBLE.

short conclusion: SQL is a really powerful programming
language, and supports many useful functions for editing
text strings.

https://www.sqlite.org/lang_corefunc.html

if you are any seriously interested into SQLite and
SpatiaLite spending an hour (or two) for studying and
testing all the core SQL functions supported by SQLite
surely is one the best investments you can do ;-)

bye Sandro

[Jukka Rahkonen]

Hi,

If you read carefully the value is NOT "182,69". The value has also
another comma at the end "182,69,"
Because you have proper value for example for the community of Nemoli I
would first check the source data. For debugging it could help to save
data from Excel as csv or some other text format.

-Jukka Rahkonen-

[antonio valanzano]

Thanks for the answer.
I was wrong about describing the situation.
The bad value was 189,69, (with two commas, as Jukka pointed out)

What surprised me is that although it is a text when I tried to find the  min and max  such value was considered a valid real number and it was recognized as the max value in the table.
That's the reason for my question about how to intercept this situation.

For the question of commas and points I can tell that all the other values are with commas (just one) and were read correctly during the import.

Antonio

[a.fu...@lqt.it]

On Wed, 29 Nov 2017 06:14:25 -0800 (PST), antonio valanzano wrote:
> What surprised me is that although it is a text when I tried to find
> the  min and max  such value was considered a valid real number and
> it was recognized as the max value in the table.
>

Antonio,

it's not true that it was considered as a valid real number;
it was considered exactly as a Text string.
counter-check:

CREATE TABLE test (value DOUBLE);
INSERT INTO test VALUES (9999.99);
INSERT INTO test VALUES ('1 abc');
SELECT Min(value), Max(value) FROM test;
=========================
9999.990000 1 abc

when evaluating a mixed-type sequence containing both
numbers and literals all numbers will be always considered
to be "lesser" than any text string.

ciao Sandro

[antonio valanzano]

Thanks a lot for this clarification... I didn't know such problem with mixed-type values and which one is lesser than the other.

Antonio