CODEWITHSUNDEEP
oledbadoxlsxxlsms-jet-ace
user3715238
user3715238

Reputation:

OLEDB not reading all rows from XLSX under Windows 10 and Microsoft office 2013

I have XLSX file with 243k rows.

Same program gives different result on diffrent computers.

If i open it on Windows 8.1 + Office 2010, program reads all of 243k Rows and all works fine.

Under Windows 10+Office 2013 it reads only first 237k Rows, truncating last 6k rows.

Im using Delphi, with following connection string

ADOConnection1.ConnectionString:='Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\File.xlsx;Extended Properties="Excel 12.0;HDR=NO;IMEX=1"';
ADOQuery1.SQL.Text:='SELECT * FROM Sheet1$'
ADOQuery1.Open;
ShowMessage(ADOQuery1.RecordCount.ToString);

Upvotes: 0

Views: 140

Answers (1)

user3715238
user3715238

Reputation:

Problem was with table indexies and i messed up with code a bit. Query text was taking first table name from table list instead of constant.

ADOQuery1.SQL.Text:='SELECT * FROM ' However, table indexes are different from version to version. So selection was from different tables

Windows 8.1+Office2010 table list:

_xlnm#_FilterDatabase
_xlnm#Database
_xlnm#Print_Area
Instruction$
CATALOG$

Windows 10+Office2013 table list

_xlnm#Database
Instruction$
Instruction$_xlnm#Print_Area
CATALOG$
CATALOG$_xlnm#_FilterDatabase

But if i open file in Excel, there are only 2 sheets named : "Instruction" and "Catalog".

I dont know where the 237k rows are came from.

Upvotes: 0

Related Questions