Problem in reading different data types....

[Ramesh]

When i tried to read data from Excel,I have come with a strange
problem.........
I am not able to read the mixed data in one column.
Ex:
Col1
Ro1 10
Ro2 Test
Ro3 11
Ro4 12

When i tried to read Ro1 ,i was able to read data but when i tried to read
Ro2 i am getting null value.
I am using Provider as Microsoft.Jet.OLEDB.4.0
Its always depend on the Ro1.If i have text in Ro1(as First vale) i could
read all text values in other rows of Col1 .If i have numeric value in
Ro1(As first value) i could read only numeric data for all other rows for
tht column.

Thank u very much.,
rgds,
ramesh....

 

[Chris Nicholas]

Ramesh,

This is actually documented in Article 190195 HOWTO: Extract Information
from Excel Sheet with DAO
http://support.microsoft.com/?id=190195. The problem is with the ISAM
driver and how it determines the datatype that it will use to collect the
data. Unfortunately this known limitation is not going away anytime soon
that I know of.

Now in order to correct this you will need to reset the datatype for all
data to be the same. Based on your example data if you preceed the numbers
with a apostrophe the datatype is altered to Text and all will show up when
you query. Note that just setting the Format is not what is needed here as
that doesn't actually change the datatype that any of the Data Access
processes get provided.

Chris Nicholas, MCSE
Microsoft Excel Support Professional
(e-mail address removed)