Fix ACE.OLEDB.12.0 Limiting Excel Queries to 65,535 Rows
Question details
Users experience a row truncation issue where querying an Excel file using the ACE.OLEDB.12.0 provider only returns 65,535 rows, despite the file containing over a million rows.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Querying large Excel datasets via the ACE.OLEDB.12.0 provider in a custom application, script, or database tool.
- Observed behavior
- The query silently truncates the dataset, returning a maximum of exactly 65,535 rows instead of the full dataset available in the modern Excel format.
Ensure you have administrative privileges to install or update database drivers on your system, and verify the exact bit-version (32-bit or 64-bit) of the application executing the query.
Update and Verify Microsoft Access Database Engine Architecture
Installing the latest driver and matching the architecture (32-bit or 64-bit) to your application often resolves row limitation issues caused by outdated components.
The 65,535 row limit is a remnant of the older Excel 97-2003 (.xls) format. If the OLEDB provider is outdated or mismatched in architecture, it may mistakenly treat newer .xlsx files as legacy formats.
Determine whether the application or script environment executing the OLEDB query is running in 32-bit or 64-bit mode.
Navigate to the official Microsoft download center and search for the latest 'Microsoft Access Database Engine Redistributable'.
Download and install the architecture version (32-bit or 64-bit) that strictly matches your application's environment, not necessarily your Windows OS architecture.
Restart your application or IDE, then re-run the query to confirm that data beyond 65,535 rows is successfully retrieved.
Adjust Connection String Extended Properties
Modifying the connection string parameters can help the OLEDB provider correctly interpret the data structure and bypass format misidentifications.
Handle Large Datasets Smoothly with WPS Office
If you are struggling with Microsoft driver limits or legacy Excel compatibility issues, WPS Office offers a modern, lightweight, and highly compatible alternative. It natively handles worksheets with over a million rows without requiring complex OLEDB driver configurations.
- 1. Download WPS Office: Visit the official WPS Office website and download the free software suite.
- 2. Install the Software: Run the lightweight installer and open WPS Spreadsheet on your computer.
- 3. Open Your Large Dataset: Open your .xlsx file directly to view, analyze, and manage all 1,048,576 rows without OLEDB driver restrictions.
Frequently Asked Questions
Why does ACE.OLEDB.12.0 stop at exactly 65,535 rows?
The number 65,535 corresponds to the maximum row limit of the older Excel 97-2003 (.xls) format. The OLEDB provider might be misinterpreting the modern .xlsx file as an older .xls file due to outdated drivers, incorrect extended properties in the connection string, or a mismatch in architecture.
Does setting IMEX=1 always fix the row limit issue?
No. IMEX=1 is primarily intended to handle columns with mixed data types by reading them as text. If your data is purely numeric, setting IMEX=1 may not change the row limitation result. In such cases, updating the driver architecture is the recommended solution.
How do I know if I need the 32-bit or 64-bit Access Database Engine?
The required architecture depends entirely on the application executing the query, not your Windows operating system. If you are using a 32-bit Python environment, a 32-bit C# application, or a 32-bit Office installation, you must install the 32-bit database driver.
