Board index » delphi » tADODataSet and Excel

tADODataSet and Excel


2004-01-28 03:25:14 PM
delphi223
Hi,
I am having a strange problem extracting fields from a table in an Excel
(XLS) file. It works fine on my Win98 development machine using ADO
version 2.7 however when I shift it to my Windows 2000 machine using ADO
version 2.5 some of the fields come up blank. I use D7.
I iterate through each record in the file using the following code -
while not Eof do
begin
ShowMessage(Fields[Ord(upCode)].AsString + ',' +
Fields[Ord(upLast)].AsString + ',' +
Fields[Ord(upName)].AsString + ',' +
Fields[Ord(upBuy)].AsString + ',' +
Fields[Ord(upSel)].AsString + ',' +
Fields[Ord(upCPU)].AsString + ',' +
Fields[Ord(upPPA)].AsString + ',' +
Fields[Ord(upBCD)].AsString + ',' +
Fields[Ord(upEDP)].AsString);
Next;
end;
As I said on my machine it all looks fine but when I process the same
file on the Windows 2000 machine the text fields display fine but the
number/date fields display as a blank.
Any ideas on why, just thought someone might have bumped into this one
before.
Thanks,
Michael.
 
 

Re:tADODataSet and Excel

I can not pinpoint your problem exactly but I'd suggest upgrading the
other machine to MDAC 2.7 and try this process again.
 

Re:tADODataSet and Excel

Brian,
Thanks, I tried that but still the same ~ very strange.
Thanks,
Michael.
Brian Hollister writes:
Quote
I can not pinpoint your problem exactly but I'd suggest upgrading the
other machine to MDAC 2.7 and try this process again.


 

Re:tADODataSet and Excel

Further to my post on this problem, I have just done a comparison of the
datatype of each field in the Excel table using the following simple code -
for J := 0 to FieldCount - 1 do
ShowMessage(FieldTypeNames[FieldDefs.Items[J].DataType]);
On my 98 machine the fieldtypes display as -
WideString, WideString, DateTime, DateTime, WideString, Float, etc.
Yet on the 2000 machine they all display as WideString.
Does this trigger any ideas in anyone ?
Thanks,
Michael.
Michael Beardmore writes:
Quote
Hi,

I am having a strange problem extracting fields from a table in an
Excel (XLS) file. It works fine on my Win98 development machine using
ADO version 2.7 however when I shift it to my Windows 2000 machine using
ADO version 2.5 some of the fields come up blank. I use D7.

I iterate through each record in the file using the following code -
while not Eof do
begin
ShowMessage(Fields[Ord(upCode)].AsString + ',' +
Fields[Ord(upLast)].AsString + ',' +
Fields[Ord(upName)].AsString + ',' +
Fields[Ord(upBuy)].AsString + ',' +
Fields[Ord(upSel)].AsString + ',' +
Fields[Ord(upCPU)].AsString + ',' +
Fields[Ord(upPPA)].AsString + ',' +
Fields[Ord(upBCD)].AsString + ',' +
Fields[Ord(upEDP)].AsString);
Next;
end;

As I said on my machine it all looks fine but when I process the same
file on the Windows 2000 machine the text fields display fine but the
number/date fields display as a blank.

Any ideas on why, just thought someone might have bumped into this one
before.

Thanks,

Michael.

 

Re:tADODataSet and Excel

What ADO driver do you use for MS Excel?
Maybe on two computers you installed different drivers (or versiosn for this
driver)?
I sure that problem is not in version of ADO. The version of driver is more
important.
"Michael Beardmore" <XXXX@XXXXX.COM>writes
Quote
Further to my post on this problem, I have just done a comparison of the
datatype of each field in the Excel table using the following simple
code -
for J := 0 to FieldCount - 1 do
ShowMessage(FieldTypeNames[FieldDefs.Items[J].DataType]);
On my 98 machine the fieldtypes display as -
WideString, WideString, DateTime, DateTime, WideString, Float, etc.
Yet on the 2000 machine they all display as WideString.

Does this trigger any ideas in anyone ?

Thanks,

Michael.

Michael Beardmore writes:

>Hi,
>
>I am having a strange problem extracting fields from a table in an
>Excel (XLS) file. It works fine on my Win98 development machine using
>ADO version 2.7 however when I shift it to my Windows 2000 machine using
>ADO version 2.5 some of the fields come up blank. I use D7.
>
>I iterate through each record in the file using the following code -
>while not Eof do
>begin
>ShowMessage(Fields[Ord(upCode)].AsString + ',' +
>Fields[Ord(upLast)].AsString + ',' +
>Fields[Ord(upName)].AsString + ',' +
>Fields[Ord(upBuy)].AsString + ',' +
>Fields[Ord(upSel)].AsString + ',' +
>Fields[Ord(upCPU)].AsString + ',' +
>Fields[Ord(upPPA)].AsString + ',' +
>Fields[Ord(upBCD)].AsString + ',' +
>Fields[Ord(upEDP)].AsString);
>Next;
>end;
>
>As I said on my machine it all looks fine but when I process the same
>file on the Windows 2000 machine the text fields display fine but the
>number/date fields display as a blank.
>
>Any ideas on why, just thought someone might have bumped into this one
>before.
>
>Thanks,
>
>Michael.
>

 

Re:tADODataSet and Excel

Mike,
Thanks.
Not sure what the driver is. In my ConnectionString I set the
Provider=Microsoft.Jet.OLEDB.4.0
If this is the driver you are talking about then I have upgraded this on
the 2000 machine. If you are talking about some other driver then please
advise which one.
Sorry I am not well versed with ADO, this is the first time I have ever
used it.
Thanks again,
Michael.
Mike Shkolnik writes:
Quote
What ADO driver do you use for MS Excel?
Maybe on two computers you installed different drivers (or versiosn for this
driver)?

I sure that problem is not in version of ADO. The version of driver is more
important.
--
With best regards, Mike Shkolnik
E-mail: XXXX@XXXXX.COM
WEB: www.scalabium.com

"Michael Beardmore" <XXXX@XXXXX.COM>writes
news:XXXX@XXXXX.COM...

>Further to my post on this problem, I have just done a comparison of the
>datatype of each field in the Excel table using the following simple
>
code -

>for J := 0 to FieldCount - 1 do
>ShowMessage(FieldTypeNames[FieldDefs.Items[J].DataType]);
>On my 98 machine the fieldtypes display as -
>WideString, WideString, DateTime, DateTime, WideString, Float, etc.
>Yet on the 2000 machine they all display as WideString.
>
>Does this trigger any ideas in anyone ?
>
>Thanks,
>
>Michael.
>
>Michael Beardmore writes:
>
>>Hi,
>>
>>I am having a strange problem extracting fields from a table in an
>>Excel (XLS) file. It works fine on my Win98 development machine using
>>ADO version 2.7 however when I shift it to my Windows 2000 machine using
>>ADO version 2.5 some of the fields come up blank. I use D7.
>>
>>I iterate through each record in the file using the following code -
>>while not Eof do
>>begin
>>ShowMessage(Fields[Ord(upCode)].AsString + ',' +
>>Fields[Ord(upLast)].AsString + ',' +
>>Fields[Ord(upName)].AsString + ',' +
>>Fields[Ord(upBuy)].AsString + ',' +
>>Fields[Ord(upSel)].AsString + ',' +
>>Fields[Ord(upCPU)].AsString + ',' +
>>Fields[Ord(upPPA)].AsString + ',' +
>>Fields[Ord(upBCD)].AsString + ',' +
>>Fields[Ord(upEDP)].AsString);
>>Next;
>>end;
>>
>>As I said on my machine it all looks fine but when I process the same
>>file on the Windows 2000 machine the text fields display fine but the
>>number/date fields display as a blank.
>>
>>Any ideas on why, just thought someone might have bumped into this one
>>before.
>>
>>Thanks,
>>
>>Michael.
>>


 

Re:tADODataSet and Excel

Michael Beardmore writes:
Quote
Mike,

Thanks.
Not sure what the driver is. In my ConnectionString I set the
Provider=Microsoft.Jet.OLEDB.4.0
And "Extended Properties="Excel 8.0;HDR=Yes;", right?
--
jc
Remove the -not from email
 

Re:tADODataSet and Excel

It sounds for me as known problem with mixed value columns in Excel (i.e.
columns with both numerical, datetime and character data). If this is the
case then you can try to add "IMEX=1;" to extended properties of yours
connection string.
"Michael Beardmore" <XXXX@XXXXX.COM>???????/???????? ? ????????
?????????: news:XXXX@XXXXX.COM...
Quote
Hi,

I am having a strange problem extracting fields from a table in an Excel
(XLS) file. It works fine on my Win98 development machine using ADO
version 2.7 however when I shift it to my Windows 2000 machine using ADO
version 2.5 some of the fields come up blank. I use D7.

I iterate through each record in the file using the following code -
while not Eof do
begin
ShowMessage(Fields[Ord(upCode)].AsString + ',' +
Fields[Ord(upLast)].AsString + ',' +
Fields[Ord(upName)].AsString + ',' +
Fields[Ord(upBuy)].AsString + ',' +
Fields[Ord(upSel)].AsString + ',' +
Fields[Ord(upCPU)].AsString + ',' +
Fields[Ord(upPPA)].AsString + ',' +
Fields[Ord(upBCD)].AsString + ',' +
Fields[Ord(upEDP)].AsString);
Next;
end;

As I said on my machine it all looks fine but when I process the same
file on the Windows 2000 machine the text fields display fine but the
number/date fields display as a blank.

Any ideas on why, just thought someone might have bumped into this one
before.

Thanks,

Michael.

 

Re:tADODataSet and Excel

It could be different registry settings. On each machine, check the
following keys:
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes
TypeGuessRows: setting the value to 0 (zero) will force ADO to scan
all column values before choosing the appropriate data type.
ImportMixedTypes: should be set to value 'Text' i.e. import mixed-type
columns as text:
Using IMEX=1 in the connection string ensures the registry setting is
applied.
Jeremy Collins <XXXX@XXXXX.COM>writes news:<4018c288$XXXX@XXXXX.COM>...
Quote
Michael Beardmore writes:

>Mike,
>
>Thanks.
>Not sure what the driver is. In my ConnectionString I set the
>Provider=Microsoft.Jet.OLEDB.4.0

And "Extended Properties="Excel 8.0;HDR=Yes;", right?
 

Re:tADODataSet and Excel

Hi all,
Thanks to all thoes who offered suggestions to fix my problem. In trying
all suggestions and tracking down the problems I stumbled accross this
solution.
The problem was caused by different data types in each field as
correctly pointed out by Vitali Kalinin however the problem was that my
98 machine was reading the type correctly but my 2000 machine wasn't. On
searching the Microsoft web site for solutions I came accross the fact
that when data is loaded, its type is guessed based on the average type
contained in the number of rows specified in 'TypeGuessRows' and
commencing with the first row. If the guess is wrong then when trying to
extract data from a field with an incorrect datatype you would only get
a null.
On my 98 machine, TypeGuessRows was set to 25 (even though Microsoft
specifies a maximum of 16) and on my 2000 machine it was set to 8.
Increasing it to the same as the other machine fixed the problem.
For anyone interested, according to Microsoft, TypeGuessRows is found in
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry
entry. It can be set to any value from 0 to 16 where 5 would sample the
first 5 rows and 12 would sample the first 12 rows. 0 would sample all rows.
Thanks again,
Michael.
Michael Beardmore writes:
Quote
Hi,

I am having a strange problem extracting fields from a table in an
Excel (XLS) file. It works fine on my Win98 development machine using
ADO version 2.7 however when I shift it to my Windows 2000 machine using
ADO version 2.5 some of the fields come up blank. I use D7.

I iterate through each record in the file using the following code -
while not Eof do
begin
ShowMessage(Fields[Ord(upCode)].AsString + ',' +
Fields[Ord(upLast)].AsString + ',' +
Fields[Ord(upName)].AsString + ',' +
Fields[Ord(upBuy)].AsString + ',' +
Fields[Ord(upSel)].AsString + ',' +
Fields[Ord(upCPU)].AsString + ',' +
Fields[Ord(upPPA)].AsString + ',' +
Fields[Ord(upBCD)].AsString + ',' +
Fields[Ord(upEDP)].AsString);
Next;
end;

As I said on my machine it all looks fine but when I process the same
file on the Windows 2000 machine the text fields display fine but the
number/date fields display as a blank.

Any ideas on why, just thought someone might have bumped into this one
before.

Thanks,

Michael.

 

Re:tADODataSet and Excel

Jeremy,
Thanks for the suggestion, I do use "Extended Properties=Excel 8.0" but
not "HDR=Yes" so I tried it. Unfortunately I got a "Could not find
installable ISAM" error message. I started down the road of tracking
this problem and got to re-installing Office and then checked the other
machine to see if the same error cropped up. It did so I felt that
re-installing Office was not going to fix the problem as it existed on
both computers. However in tracking this problem down I managed to
figure out the differences between the two computers and fixing this
fixed the problem.
See final post.
Thanks,
Michael.
Jeremy Collins writes:
Quote
Michael Beardmore writes:

>Mike,
>
>Thanks.
>Not sure what the driver is. In my ConnectionString I set the
>Provider=Microsoft.Jet.OLEDB.4.0


And "Extended Properties="Excel 8.0;HDR=Yes;", right?

 

Re:tADODataSet and Excel

Vitali,
You are absolutely correct in your assesment of my problem, I did try
adding "IMEX=1" to the Connect String but I got a "Could not find
installable ISAM" error message. I started down the road of tracking
this problem and got to re-installing Office and then checked the other
machine to see if the same error cropped up. It did so I felt that
re-installing Office was not going to fix the problem as it existed on
both computers. However in tracking this problem down I managed to
figure out the differences between the two computers and fixing this
fixed the problem.
See final post.
Thanks for your help,
Michael.
Vitali Kalinin writes:
Quote
It sounds for me as known problem with mixed value columns in Excel (i.e.
columns with both numerical, datetime and character data). If this is the
case then you can try to add "IMEX=1;" to extended properties of yours
connection string.

"Michael Beardmore" <XXXX@XXXXX.COM>???????/???????? ? ????????
?????????: news:XXXX@XXXXX.COM...

>Hi,
>
>I am having a strange problem extracting fields from a table in an Excel
>(XLS) file. It works fine on my Win98 development machine using ADO
>version 2.7 however when I shift it to my Windows 2000 machine using ADO
>version 2.5 some of the fields come up blank. I use D7.
>
>I iterate through each record in the file using the following code -
>while not Eof do
>begin
>ShowMessage(Fields[Ord(upCode)].AsString + ',' +
>Fields[Ord(upLast)].AsString + ',' +
>Fields[Ord(upName)].AsString + ',' +
>Fields[Ord(upBuy)].AsString + ',' +
>Fields[Ord(upSel)].AsString + ',' +
>Fields[Ord(upCPU)].AsString + ',' +
>Fields[Ord(upPPA)].AsString + ',' +
>Fields[Ord(upBCD)].AsString + ',' +
>Fields[Ord(upEDP)].AsString);
>Next;
>end;
>
>As I said on my machine it all looks fine but when I process the same
>file on the Windows 2000 machine the text fields display fine but the
>number/date fields display as a blank.
>
>Any ideas on why, just thought someone might have bumped into this one
>before.
>
>Thanks,
>
>Michael.
>


 

Re:tADODataSet and Excel

Quote
For anyone interested, according to Microsoft, TypeGuessRows is found in
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry
entry. It can be set to any value from 0 to 16 where 5 would sample the
first 5 rows and 12 would sample the first 12 rows. 0 would sample all
rows.
Further; there is a JET Isam gui built into adoanywhere browser that
displays all your Jet registry settings (and lets you modify them).
Screenshot www.adoanywhere.com/help/preferences.htm
--
Mike Collier
FREE ADO Inspector tool when you register at ADO forum.
www.adoanywhere.com
www.adoanywhere.com/forum