Nulls vs. blanks?
 

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!

Nulls vs. blanks?
 

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









"DesertCyclist" wrote in message
...
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!

Nulls vs. blanks?
 

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",A 1)="'"),-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.
--
Gary''s Student - gsnu200770


"DesertCyclist" wrote:

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!

Nulls vs. blanks?
 

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" wrote in message
...
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!

Nulls vs. blanks?
 

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 :)

"DesertCyclist" wrote:

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!