Search code examples
c#excelselectexcel-2007oledb

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

Solution

  • 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.