CSV is unable to represent the NULL value. The best it can do is "empty string", which gets converted to - drumroll - an empty string (or the value 0 for numeric fields).
The field names you are using suggest that you may have not sufficiently normalized your data, since you have duplicated postcode and city fields. Have you considered that the same person might be the contact for more than one shop? -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Winfried Gesendet: Donnerstag, 21. November 2019 12:32 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] [Importing CSV] Empty colums != NULL? Hello, I imported data from a CSV file. Some of the columns are empty, so I expected them to be set to NULL, but it looks like it's not how SQLite handles them internally: ========= sqlite> .schema CREATE TABLE Shops( "id" TEXT, "name" TEXT, "contact:postcode" TEXT, "contact:city" TEXT, "addr:postcode" TEXT, "addr:city" TEXT, "contact:phone" TEXT, "email" TEXT, "website" TEXT ); ========= Here's a record where "addr:postcode" is filled, but "contact:postcode" is not: ========= sqlite> select * from Shops limit 10; id;name;contact:postcode;contact:city;addr:postcode;addr:city;contact:phone;email;website 251373376;Acme;;;12345;My City;;;http://www.acme.com etc. ========= But SQLite returns an empy column when using IFNULL: ========= sqlite> select name, IFNULL("contact:postcode","addr:postcode") zipcode sqlite> from Shops where id="251373376"; name;zipcode Acme; ========= Should I use a different command when importing data or running IFNULL? Thank you. -- Sent from: http://sqlite.1065341.n5.nabble.com/ _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
