How Do I read an Excel 2007 spreadsheet with more than 65535 rows?

Here's what my connection string looks like..

m_conn = new OleDbConnection(String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=" + (char)34 + "Excel 12.0;HDR=YES" + (char)34, m_fileName));

When I do a select, I only get 65535 rows back but I know the excel spreadsheet has at least 100,000. How Do I read the rest of the rows. Note I have to use OLEDB, 开发者_运维技巧no 3rd party tools right now.

Thanks much!

edit: These are Excel 2007/2010 files I'm working with

edit2: Would seem I can actually select all the rows in the spreadsheet if I do a "Select * from worksheet" as opposed to "Select * from worksheet WHERE something"

I guess when you implement a WHERE clause, the resulting rows is capped at 65535

As per my experience, an Excel worksheet is limited to 65535 lines (Excel up to 2003, I don't know about the 2007 version).

EDIT #1

My guess is that the OleDB data provider perhaps needs an update to correct this issue issued from the change of this Excel's limit.

If you only want to read an Excel file, I recommend trying the open-source Excel Data Reader.

You should open such files and save them with compatibility mode turned off. You will get more than 64k in your queries.