[Qgis-user] Distincguis NULL and empty strings when reading CSV files

[Brent.Wood at niwa.co.nz]

Not that I'm aware of -

Text files do not have an implicit way of representing null values, except for an empty string.

Databases do this better, with an isnull() function which is different to an ='' (2x single quotes) expression - the empty string.

Some software packages use a locally specified string (eg: NA) to represent nulls, so you can use an !='NA' expression to exclude nulls.

The ^A (control-A) ANSI character also represents a null, but good luck getting a useful text file containing control codes. Generally non-trivial.

Note that an "=null" expression is semantically void, null means unknown, so two nulls do not equal each other, we don't know what they equal by definition.
Which is why SQL has the is null/isnull() approach, it is not a logical "=" operator.

I suggest you either load your text file into a database table (spatialite or postgis?), define the nulls as db null values (which are NOT the same as empty strings)
& QGIS can use the db to do the null operations, or alternatively you could define a string (not an empty string) that is used to represent nulls in your file, and use an "=" operator. For numerical values you can use a value which does not occur in the dataset to represent a null .

Brent Wood

Programme leader: Environmental Information Delivery
NIWA
DDI:  +64 (4) 3860529

________________________________________
From: Qgis-user <qgis-user-bounces at lists.osgeo.org> on behalf of Yann Voté <ygversil at lilo.org>
Sent: Wednesday, April 15, 2020 21:57
To: qgis-user at lists.osgeo.org
Subject: [Qgis-user] Distincguis NULL and empty strings when reading CSV        files

Hi all,

Just wondering if there is a way to distinguish null values and empty
strings when loading CSV files in QGIS.

What I tried so far is quoting empty strings like in the following example.

col1,col2,col3
1,,""

I was expecting col2 to be NULL and col3 to be an empty string. But
actual result is that both col2 and col3 are NULL.

Is there another way ? Am I missing something ?

Thanks in advance.

Yann
_______________________________________________
Qgis-user mailing list
Qgis-user at lists.osgeo.org
List info: https://aus01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fqgis-user&data=02%7C01%7Cbrent.wood%40niwa.co.nz%7Cd4ca1af8180e4d000d9e08d7e1237944%7C41caed736a0c468aba499ff6aafd1c77%7C0%7C0%7C637225414814189476&sdata=Kn4huzdkXPJpZK86xwhz9o17TlIhyzB6Ph72Br37sF4%3D&reserved=0
Unsubscribe: https://aus01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fqgis-user&data=02%7C01%7Cbrent.wood%40niwa.co.nz%7Cd4ca1af8180e4d000d9e08d7e1237944%7C41caed736a0c468aba499ff6aafd1c77%7C0%7C0%7C637225414814189476&sdata=Kn4huzdkXPJpZK86xwhz9o17TlIhyzB6Ph72Br37sF4%3D&reserved=0
[https://www.niwa.co.nz/static/niwa-2018-horizontal-180.png]<https://www.niwa.co.nz>
 Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529 |
National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade Hataitai Wellington New Zealand
Connect with NIWA: niwa.co.nz<https://www.niwa.co.nz> Facebook<https://www.facebook.com/nzniwa> LinkedIn<https://www.linkedin.com/company/niwa> Twitter<https://twitter.com/niwa_nz> Instagram<https://www.instagram.com/niwa_science>
To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems