jeudi 16 avril 2015

When reading DBF file into SQLite/Spatialite, convert empty records to NULL instead of zero/0

I use the Virtual Table functionality of SQLite together with Spatialite's VirtualDbf extension (slightly mentioned here) to import a DBF file into my SQLite database with Spatialite extensions enabled like so:



CREATE VIRTUAL TABLE myTableName USING VirtualDbf(pathTo.dbf, CP1252)


Unfortunately, my DBF file contains empty values which do not contain anything, see the following example:


Example row of dbf file


Importing such a file with the VirtualDbf command into a database table with numeric columns leads to all empty values from the DBF file being replaced by 0 which is not what I want. I would rather expect them to be NULL instead because 0 is not distinguishable anymore from "real" 0 values.


I understand that DBF files are not made for storing empty/NULL values because there seems to be no common standard on how to handle them, hence every application decides for itself whether to replace with 0, NULL or something completely different.


Still, is there any way to tweak this behaviour to replace empty values with NULL?


If not, is there another approach to read DBF files into a SQLite database which is as easy and fast as using VirtualDbf?


As a workaround, I currently convert the DBF files to .csv first and then use VirtualText which surprisingly handles empty values as NULL. Apart from that, I could iterate over the DBF file, replace all empty values with something like 999999 and then replace all 999999 with NULL in the database table but I would prefer a direct way.


Aucun commentaire:

Enregistrer un commentaire