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

Wed Apr 15 04:40:50 PDT 2020

Brent : thanks for the detailed explanation. As for your suggestion, I'm 
trying to go the other way around: Postgis --> CSV --> QGIS.

The goal is to quickly extract data from a very large table in Postgis 
using an extent drawn by the user on the canvas. So I do a `COPY (SELECT 
... FROM ... WHERE ST_Intersects(geometry, extent_drawn_on_canvas)) TO 
...` to get a CSV file, and then I load the CSV file into QGIS, 
expecting to get the exact same values than in Postgis (NULL when NULL 
in Postgis, empty string when empty string in Postgis).

Richard: nice workaround, and it almost works! With a space in double 
quotes (" "), I get in my QGIS 3.12 a single space string, not an empty 
string, even when Trim fields in checked. But with a space and no 
quotes, it works! That is just like the following.

col1,col2,col3,col4
1,, ,"foo"

col3 get an empty string (if Trim fields is checked).

Hope this is a stable behavior.

Thanks again!

Regards.

Yann

Le 15/04/2020 à 12:35, Brent.Wood at niwa.co.nz a écrit :
> 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
> 