> -----Original Message----- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: Monday, October 02, 2006 9:26 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] import syntax and null values > > > Rich Shepard wrote: > > On Mon, 2 Oct 2006, Eric Matthew Finnin wrote: > > > >> Could someone tell me the syntax for the .import command? I can't > >> seem to > >> get my null values to be recognized. > > > > .nullvalue STRING Print STRING in place of NULL values > > > > If you type '.h' you'll see the syntax of all the commands. > > > > Rich > > > Rich and Eric, > > The .nullvalue command won't work. It only affects the way the sqlite > shell handles null values on output. In fact there is no > direct way to > insert NULL values into a table using the .import command. It > reads all > its input as strings, and the closest you can get is a empty > string (by > placing two separators back to back) which isn't the same as a null > values. It doesn't use the .nullvalue string on input. > > If you know that the string 'NULL' does not appear in your > valid data, > you can use a series of update statements to replace that string with > real null values. You could, of course, use any other string as a > sentinel value to hold the place of your NULLs. > > .import test1.csv test1 > update test1 set col2 = NULL where col2 = 'NULL'; > update test1 set col3 = NULL where col3 = 'NULL'; > > Or you could modify the source of the .import command so that it does > recognize the .nullvalue string on input, and binds a null value for > that column. If you do you should submit a patch, others may > find this > useful. > > HTH > Dennis Cote
Would it not be more logical to modify the .import command to place a null value in a column where the input value is non existent? (i.e. Back to back separators in the input) Null in, null out. Fred ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
