Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nulls in Pivot Tables | Excel Programming | |||
Nulls within a .Find command | Excel Programming | |||
deaking with nulls, I think... | Excel Programming | |||
Using Excel, how do I replace cells containing blanks with nulls? | Excel Discussion (Misc queries) | |||
Paste Special Skip Blanks not skipping blanks, but overwriting... | Excel Discussion (Misc queries) |