2

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?

RBA
  • 12,337
  • 16
  • 79
  • 126

3 Answers3

2

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

Brian
  • 6,717
  • 2
  • 23
  • 31
  • PRB: Excel Values Returned as NULL Using DAO OpenRecordset - this was the answer. – RBA Mar 31 '11 at 15:13
1

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.

Marjan Venema
  • 19,136
  • 6
  • 65
  • 79
  • thank you Marjan, but the problems is that I get into my query only the rows where the last 2 columns have the cells with that 'green' alert... – RBA Mar 31 '11 at 11:14
  • Then check what the green alert says. That may at least give you a hint. If it is something like "function differs from other cells in this regio" that may still indicate completely empty rows in that region... – Marjan Venema Mar 31 '11 at 11:35
1

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.

Najem
  • 537
  • 4
  • 12