How do I exclude null rows without excluding null cells when pulling Excel into C# using SQL (ADO)?

I have a query like the following:

SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=D:\test.xls','SELECT * FROM Sheet1$]')

This brings back rows that are all null if they were ever edited and then deleted. I want to exclude those, but still include rows that have good data but possible null cells.

My first instinct was to do a "WHERE each column IS NOT NULL" ANDed together, like so:

SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};   DBQ=D:\test.xls', 'SELECT * FROM Sheet1$]')
WHERE 
(
  Col1 IS NOT NULL 
  AND Col2 IS NOT NULL 
  AND Col3 IS NOT NULL 
  AND Col4 IS NOT NULL 
)

This effectively eliminates the null rows, but for some reason, also eliminates a row where Col4 had a null entry. I tried this with and without parens around the WHERE clause.

Does anyone see what I might be doing wrong, or in lieu of that, have a suggestion of a different method I can use to achieve the same results?

This is currently being implemented in C# using ADO, but I am testing the query using Sql Server Management Studio 2008.

Because this is OR'd, won't it then exclude a row where only one of the columns is null? That is something I don't want to do.

IronicMuffin 2010-02-03 21:45:14

No, it will _include_ that row. Imagine it as if you only had the first part (col1 is not null). That would only show rows where col1 had a value. If col1 is null but col2 is not, you would skip that row. So you need to allow col1 _or_ col2 to be null, just not both. And so on.

Joel Coehoorn 2010-02-03 23:45:05

ansaurus

tags:

views:

answers:

How do I exclude null rows without excluding null cells when pulling Excel into C# using SQL (ADO)?

related questions