import syntax and null values

[Eric Matthew Finnin]

Could someone tell me the syntax for the .import command? I can't
seem to get my null values to be recognized.

First I created test1:
CREATE TABLE [test1] (
[col1] INTEGER NOT NULL,
[col2] TEXT NULL,
[col3] INTEGER NULL
)

The data for test1.csv has the following data:
col1,col2,col3
1,yes,13
2,no,NULL
3,NULL,34
4,yes,22
5,yes,25

sqlite3 -header -separator , -nullvalue NULL test.s3db ".import

test1.csv test1"

This appears successful:
sqlite> SELECT col1, col3 FROM test1;
1|13
2|NULL
3|34
4|22
5|25

But then I get the following results:
sqlite> SELECT * FROM test1 WHERE col3 IS NULL;

sqlite> SELECT * FROM test1 WHERE col3 IS NOT NULL;
1|yes|13
2|no|NULL
3|NULL|34
4|yes|22
5|yes|25

sqlite> SELECT * FROM test1 WHERE col3 = 'NULL';
2|no|NULL
sqlite>

And if I use SQLite Administrator, the table is imported into my
database without an error, except all NULL values take the value 0.

Thank you for any suggestions.

-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Rich Shepard]

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

[Dennis Cote]

Post by Rich Shepard

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

-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Fred Williams]

-----Original Message-----
Sent: Monday, October 02, 2006 9:26 AM
Subject: Re: [sqlite] import syntax and null values

Post by Rich Shepard

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 sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Gerry Snyder]

Post by Fred Williams
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.

A null is not the same thing as an empty string. Some things would be
simpler if it were, but it is not, and the distinction is important.

If your suggestion were implemented, there would be no way to .import
an empty string.

-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Fred Williams]

-----Original Message-----
Sent: Monday, October 02, 2006 12:42 PM
Subject: Re: [sqlite] import syntax and null values

Post by Fred Williams
Would it not be more logical to modify the .import command

to place a

Post by Fred Williams
null value in a column where the input value is non

existent? (i.e. Back

Post by Fred Williams
to back separators in the input) Null in, null out.

A null is not the same thing as an empty string. Some things would be
simpler if it were, but it is not, and the distinction is important.
If your suggestion were implemented, there would be no way
to .import
an empty string.

Don't mean to split hairs, but...

For the following, the "," is the field delimiter and '"' is the string
delimiter.

Although this may fly in the face of reality, an empty string should be
denoted with ,"", and a null value by ,,. Who is to say whether ,,
denotes an empty string or an unknown value? ,"", specifically denotes
an empty (zero length) string, where ,, truly indicates an unknown
value.

With current thinking, is the ,, string zero length or unknown?
Following current logic a numeric input value of ,, should be
interpreted as zero (0) not a null value. An unknown value is not equal
to zero, here in Texas when playing Texas Hold-em! Otherwise I would
win every hand :-)

Fred




-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Dennis Cote]

Post by Fred Williams
Don't mean to split hairs, but...
For the following, the "," is the field delimiter and '"' is the string
delimiter.
Although this may fly in the face of reality, an empty string should be
denoted with ,"", and a null value by ,,. Who is to say whether ,,
denotes an empty string or an unknown value? ,"", specifically denotes
an empty (zero length) string, where ,, truly indicates an unknown
value.
With current thinking, is the ,, string zero length or unknown?
Following current logic a numeric input value of ,, should be
interpreted as zero (0) not a null value. An unknown value is not equal
to zero, here in Texas when playing Texas Hold-em! Otherwise I would
win every hand :-)

Fred,

You are using two different separator characters. The current sqlite
shell does not do this, it uses a single separator string. All the input
fields are also strings (i.e. there are no numbers until sqlite tries to
store the string and looks at the columns affinity). It views an input
line as a sequence of strings like this:

line = input (separator input)*

If you use a comma as your separator, then any quotes that are on the
line are part of the input strings, not delimiters for the input strings.

You are assuming that the sqlite shell works like a real CSV reader when
it doesn't (it's not very smart). Now, if you were to fix the import
command so that it correctly handled CSV data (including commas and
quotes in quoted strings) that would also be a good enhancement to
sqlite. You should also submit a patch if you do this.

Dennis Cote

-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Fred Williams]

-----Original Message-----
Sent: Monday, October 02, 2006 1:44 PM
Subject: Re: [sqlite] import syntax and null values

Post by Fred Williams
Don't mean to split hairs, but...
For the following, the "," is the field delimiter and '"'

is the string

Post by Fred Williams
delimiter.
Although this may fly in the face of reality, an empty

string should be

Post by Fred Williams
denoted with ,"", and a null value by ,,. Who is to say whether ,,
denotes an empty string or an unknown value? ,"",

specifically denotes

Post by Fred Williams
an empty (zero length) string, where ,, truly indicates an unknown
value.
With current thinking, is the ,, string zero length or unknown?
Following current logic a numeric input value of ,, should be
interpreted as zero (0) not a null value. An unknown value

is not equal

Post by Fred Williams
to zero, here in Texas when playing Texas Hold-em!

Otherwise I would

Post by Fred Williams
win every hand :-)

Fred,
You are using two different separator characters. The current sqlite
shell does not do this, it uses a single separator string.
All the input
fields are also strings (i.e. there are no numbers until
sqlite tries to
store the string and looks at the columns affinity). It views
an input
line = input (separator input)*
If you use a comma as your separator, then any quotes that are on the
line are part of the input strings, not delimiters for the
input strings.
You are assuming that the sqlite shell works like a real CSV
reader when
it doesn't (it's not very smart). Now, if you were to fix the import
command so that it correctly handled CSV data (including commas and
quotes in quoted strings) that would also be a good enhancement to
sqlite. You should also submit a patch if you do this.
Dennis Cote

Dennis,

I must warn you I am a semi-retired "Consultant." And we all know that
Consultants only recommend, they never get their hands dirty with actual
work! :-) Besides, I don't consider myself on a level with the "C"
programmers involved with actual support of SQLite, only grateful for
their superior expertise.

Bringing the command line utility up to full CSV reader specifications
would greatly enhance the "industry compatibility" of SQLite and should
contribute zero to the .DLL footprint. I therefore, semi Recommend that
one of you "C" coder whizzes whip out the required changes and we will
all be grateful.

Backward compatibility could be managed with a new .command.

I seldom use the command line processor and completely abandon using it
for data import, because it was so limited in import functionality.
Everything I have imported into SQLite to date has been the output of
another database product or spreadsheet utility, and I guess I have just
been lucky each exports fully compatible .CSV files.

Since my old DOS/Unix days are fading from memory, along with most
everything else, I have found "SQLite Administrator" to be a very
capable GUI based SQLite utility, that happens to have a full featured
.CSV file import and export.

Fred




-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Eric Matthew Finnin]

Thank you for the suggestions. I had no idea this would get such a response.

Sqlite Administrator recognized my null values as zero, so that
doesn't seem to be an option. For an immediate solution, it sounds
like I need to do as Dennis suggested:
.import test1.csv test1
update test1 set col2 = NULL where col2 = 'NULL';
update test1 set col3 = NULL where col3 = 'NULL';

This seems like a slow process when importing multiple tables, but
it'll do the trick. Maybe a bash script could help me here. I've
always meant to read up on bash scripting.

Thank you all for helping a novice like myself.

-Eric

Post by Fred Williams

-----Original Message-----
Sent: Monday, October 02, 2006 1:44 PM
Subject: Re: [sqlite] import syntax and null values

Post by Fred Williams
Don't mean to split hairs, but...
For the following, the "," is the field delimiter and '"'

is the string

Post by Fred Williams
delimiter.
Although this may fly in the face of reality, an empty

string should be

Post by Fred Williams
denoted with ,"", and a null value by ,,. Who is to say whether ,,
denotes an empty string or an unknown value? ,"",

specifically denotes

Post by Fred Williams
an empty (zero length) string, where ,, truly indicates an unknown
value.
With current thinking, is the ,, string zero length or unknown?
Following current logic a numeric input value of ,, should be
interpreted as zero (0) not a null value. An unknown value

is not equal

Post by Fred Williams
to zero, here in Texas when playing Texas Hold-em!

Otherwise I would

Post by Fred Williams
win every hand :-)

Fred,
You are using two different separator characters. The current sqlite
shell does not do this, it uses a single separator string.
All the input
fields are also strings (i.e. there are no numbers until
sqlite tries to
store the string and looks at the columns affinity). It views an input
line = input (separator input)*
If you use a comma as your separator, then any quotes that are on the
line are part of the input strings, not delimiters for the
input strings.
You are assuming that the sqlite shell works like a real CSV
reader when
it doesn't (it's not very smart). Now, if you were to fix the import
command so that it correctly handled CSV data (including commas and
quotes in quoted strings) that would also be a good enhancement to
sqlite. You should also submit a patch if you do this.
Dennis Cote

Dennis,
I must warn you I am a semi-retired "Consultant." And we all know that
Consultants only recommend, they never get their hands dirty with actual
work! :-) Besides, I don't consider myself on a level with the "C"
programmers involved with actual support of SQLite, only grateful for
their superior expertise.
Bringing the command line utility up to full CSV reader specifications
would greatly enhance the "industry compatibility" of SQLite and should
contribute zero to the .DLL footprint. I therefore, semi Recommend that
one of you "C" coder whizzes whip out the required changes and we will
all be grateful.
Backward compatibility could be managed with a new .command.
I seldom use the command line processor and completely abandon using it
for data import, because it was so limited in import functionality.
Everything I have imported into SQLite to date has been the output of
another database product or spreadsheet utility, and I guess I have just
been lucky each exports fully compatible .CSV files.
Since my old DOS/Unix days are fading from memory, along with most
everything else, I have found "SQLite Administrator" to be a very
capable GUI based SQLite utility, that happens to have a full featured
.CSV file import and export.
Fred
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------

-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Dennis Cote]

Post by Fred Williams
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,

If you did that, there would be no way to enter an empty string as a
value. A NULL is not the same as an empty string.

Since the shell already defines a reasonable way to convert a NULL to a
sentinel string on output, it makes more sense to me to reuse that, than
it does create another special purpose mechanism to allow a user to
enter an empty string. If the shell recognizes the same sentinel value
on input, it can substitute a real NULL value.

The only danger I see is that the initial value of the .nullvalue
sentinel string may be an empty string (since it converts NULLs to empty
strings by default on output). In this case it would also convert empty
strings on input into NULLs, so we would have the same situation where
it's not possible to enter an empty string. To prevent this the
.nullvalue string should be initialized to a NULL and then no
substitution should be done on input unless the user changes it to some
other value. I don't think you can change the output handling (by
default a NULL and an empty string look the same). There would also need
to be a mechanism to set the .nullvalue string back to NULL to turn off
the input substitution.

Dennis Cote


-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe-CzDROfG0BjIdnm+***@public.gmane.org
-----------------------------------------------------------------------------

[Nikki Locke]

Post by Fred Williams
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.

Please don't do that. I have many databases with empty strings in them which are
NOT nulls. Indeed, I have some databases where NULL and '' are supposed to be
distinct values, with different meanings.