On Jan 23, 2008 7:54 AM, Dennis Cote <[EMAIL PROTECTED]> wrote: > Robert Wishlaw wrote: > > When .import parses an empty field in the csv file, that is , a comma > > immediately followed by another comma, is the cell, in the database, > > that corresponds to the empty field a NULL cell? > > > Robert, > > No, it inserts a text field containing an empty string.
Thank you Dennis, this is what I needed to know. > > > All the fields inserted by .import are strings. If they are inserted > into columns with numeric affinity they are converted to numeric values > when stored into the database. The column was created "AS REAL". > > > > The reason I ask is that when I query the column with > > > > SELECT COUNT(*) FROM table WHERE temp ISNULL; > > > > 0 is returned but there are 5 empty cells in that column in the csv > file. > > > > > This confirms that you don't have any NULL values in that column. My expected value of 5 is returned from the query SELECT COUNT(*) FROM table WHERE temp = ""; or SELECT COUNT(*) FROM table WHERE temp <> 0 AND NOT temp; > > > > Also when I query > > > > SELECT temp FROM table WHERE temp > 0; > > > > the empty cells are returned as well as the cells containing > > temperatures over 0. > > > > > > > This is because you are comparing a numeric value to a string. It's an > empty string, but a string none the less. All strings are greater than > any numeric value (see section 3 Comparison Expressions at > http://www.sqlite.org/datatype3.html for the details of how SQLite > compares values) so in these rows, the empty string is greater than the > literal numeric value of 0. Thank you again for your concise yet comprehensive explanation of the consequences of using .import on an empty field in a .csv file. Robert Wishlaw > > > HTH > Dennis Cote > > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > > ----------------------------------------------------------------------------- > >
