How can I dodge empty rows in an Excel file with OleDB?

Question

I start to use OleDB. I'm trying to extract a large range of random data from an Excel file (in C#). This my query :

SELECT * FROM NAMED_RANGE

But I have a lot of empty rows. I delete them in a loop but I don't really like it, is it possible to select only rows with at least one cell filled ?

Best regards,

Alex

Ronaronal

Do you have any knowledge up-front about what rows/columns might have data in them? Do you have any expectations at all about what the sheet(s) might contain? – Stavanger

The only thing recurrent is when 10 lines in a row are empty, the next lines will be empty too until the end. – Ronaronal

Answer 1

Simply change the SQL Query:

SELECT * FROM NAMED_RANGE WHERE [YourColumnTitle] IS NOT NULL

Or if [F1] is your Column:

SELECT * FROM NAMED_RANGE WHERE F1 IS NOT NULL

Thanks to mr.Reband

Lexicostatistics

If the excel file doesn't have column titles , you can reference column numbers preceded by an 'F', like WHERE F1 IS NOT NULL – Hedgcock

The problem here is that unless you know exactly how many columns are possibly to contain data, you would not know how to construct such a SQL. – Stavanger

It will be ok for an empty Column but I have empty Rows :/ – Ronaronal

@Ronaronal An empty row is nothing more than a collection of empty columns. The solution works, but is tedious because of the unknown number of columns involved. – Stavanger

@Stavanger Ok, I understand. I work on thirty columns, so I guess I have to do "F1 IS NOT NULL ... AND F30 IS NOT NULL". – Ronaronal

@Ronaronal Unfortunately, unless there is more you know about the row going in, that's the best you can do. Personally, I'd rather just select it all and do it in a code loop, but that's me. – Stavanger