CODEWITHSUNDEEP
delphiexcelado
RBA
RBA

Reputation: 12584

Delphi - Excel rows get by an ADO Query

I have the following excel file

enter image description here

I set AdoConnection.ConnectionString to 

 AdoConnection.ConnectionString :=' Provider=Microsoft.Jet.OLEDB.4.0;' +
           'Data Source=' +aFileName + ';' +
           'Extended Properties=Excel 8.0;';

where aFileName is the excel file name.

After that, with an ADOQuery component(connection set to AdoConnection) I perform a 'select * from [Sheet1$]'.

The problem is that rows 16802 and 17179 are not present in the query result,and I don't know why. All the fields from the sheet are set to general. I'm using Delphi 7.

Do you have any ideas?

LE:type of all the fields from the AdoQuery are WideString. In query are present only the rows where values from the last 2 columns have that 'green sign'. I'm not a genius in Excel, but the query should not get all the data existing in a sheet?

Upvotes: 2

Views: 6825

Answers (3)

Brian
Brian

Reputation: 7289

By default it examines the first couple rows and uses that to determine the column type. When the type it chooses is text all numeric values in that column will come across as null.

The solution is to use import mode (IMEX=1). Note that this has odd behvaiour if your also trying to update rows.

 AdoConnection.ConnectionString :=' Provider=Microsoft.Jet.OLEDB.4.0;' +
       'Data Source=' +aFileName + ';' +
       'Extended Properties="Excel 8.0;IMEX=1"';

PRB: Excel Values Returned as NULL Using DAO OpenRecordset

Upvotes: 2

Najem
Najem

Reputation: 557

The 2 row have some numerical fields in col D and E, you should change this values to string, you can change the type of cell to text.

Upvotes: 1

Marjan Venema
Marjan Venema

Reputation: 19346

The one thing that comes to mind is that among the hidden rows there are one or more rows that have no value in any cell. Excel usually takes the first entirely empty row and column to be the bounds of a data range. Data range in this case is not "just and ol'" data range but a set of rows and columns that Excel treats as a table. Which is what you do by accessing the sheet through Ado.

Upvotes: 1

Related Questions