Hi Tom, I've been testing your suggestion over the past few days and it works fine. The great advantage of using triggers is being able to perform arbitrary checks and manipulation of the raw data.
At the expense of more elaborate SQL srcipts, the CSV files can be left in a more readable form which allows them to be easy visually scanned and DIFF'ed when testing the AWK conversion programs. I didn't realise that views could be written to. Cheers, Matt --- On Sun, 14/8/11, BareFeetWare <list....@barefeetware.com> wrote: From: BareFeetWare <list....@barefeetware.com> Subject: Re: [sqlite] null handling import To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Date: Sunday, 14 August, 2011, 2:04 > On 13 Aug 2011, at 7:58am, matthew (matthew.jsoft) white wrote: > >> I was just wondering if sq3 has some kind of mechanism to parse blank >> records as null instead of empty strings. One way to do this is to import into a view and use a trigger to convert the rows how you like. For example: create table Person ( ID integer primary key not null , Name text collate nocase , Email text collate nocase ) ; create view Import as select Name, Email from Person ; create trigger "Import insert" instead of insert on Import begin insert into Person (Name, Email) select case when Name = '' then null else Name end , case when Email = '' then null else Email end ; end ; Then just import into the "Import" view instead of the table. Tom Tom Brodhurst-Hill BareFeetWare Sent from my iPhone _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
