Nulls vs. blanks?

[DesertCyclist]

I have a spreadsheet with a field that may contain blanks (empty). When I
filter on that field to show only the blanks, I get 4000 records returned.
But if I import the spreadsheet into SQL Server, the test in there for blanks
('') returns nothing. However, if I test for Nulls in that field, I get 5000
records returned. I don't know which one (Excel or SQL Server) is giving me
the right answer. Can anyone help me understand what's going on?

Thanks!

 

[Conan Kelly]

DesertCyclist,

FYI, this question is probably more appropriate for
microsoft.public.excel.misc or a SQL Server newsgroup, but I'll take a shot
at it.

When you filter records, what is the range of cells that are being filtered?
Is there data below this table being filtered? Does this table have a named
range?

When you import into SQL Server, are you importing by sheet name or by a
named range?

In XL, do a [Ctrl] + [End] key combination. What cell do you end up at?
Are you 1000+ rows below your last row of data? Maybe you are importing a
bunch of blank rows into SQL Server.

Write back, answering those questions and check the last cell in XL. I
might be able to help you figure this out.

HTH,

Conan

 

[Gary''s Student]

If you have a cell that appears to be empty, you can test it with the
following formula (the Rosenfeld Formula):

=IF(ISBLANK(A1),0,IF(AND(LEN(A1)=0,CELL("prefix",A1)="'"),-2,IF(LEN(A1)=0,-1,CODE(A1))))

the formula will return 0 if A1 is genuinely empty
the formula will return -1 if A1 has a fomula returning null ("")
the formula will return -2 if A1 has a single apostrophe in it
the formula will return 32 is A1 has a space in it

The formula will return the ASCII value of the character otherwise.

Even though a cell LOOKS empty, the formula above will tell you whats really
in it.

 

[Harald Staff]

This is belief, not knowledge.
An empty spreadsheet cell contains both numeric value 0 and an empty string.
A database field with nothing in it contains NULL , meaning "unknown", which
is not the far more known 0 or empty string.

So who's right? Excel of course ;-)

HTH. Best wishes Harald

 

[DesertCyclist]

Thank you all for the replies and insights. As it turns out, we re-imported
the spreadsheet and everything is fine now. Maybe some hic-up in the import
process? Who knows?... Thanks again, though