I'm currently using the Microsoft ADO excel driver, and
I'm having difficulty setting the data type when I
retrieve the data from file. The automatic type detecting
feature on the ADO driver is not doing a good job.

Any one know a good driver for reading data from an MS
Excel file? Suggestions please.

This may or may not be helpful - my experience:

A built-in limitation of ADO is that it can only pull in
one data type from any given column. It samples the first
few rows and whichever data type is prevalent wins. The
driver then only retrieves that data type from that
column.

So for example, if you have text in your first eight or
ten rows and 30,000 values beneath that, only the text
will be retrieved.

The only solution is to be sure that none of your
critical columns contains mixed types.

I worked around this by:

1. Turn off screen updating
2. Open the source file conventionally - read only
3. Copy the data of choice
4. Close the file without saving changes

This may seem less punchy and cutting edge but, it ALWAYS
works and it's less code intensive.

I have also found ADO to be unstable, due to its driver
dependency. Sometimes it works, then you get new drivers,
and then it quits again. I got tired of supporting it and
switched practically all my code over.

Hope this helps somehow...


-----Original Message-----

I'm currently using the Microsoft ADO excel driver, and
I'm having difficulty setting the data type when I
retrieve the data from file. The automatic type
detecting
feature on the ADO driver is not doing a good job.

Any one know a good driver for reading data from an MS
Excel file? Suggestions please.




.

Thank PK, your comment is good.

However, the situation at my company does not allow this solution. We
are trying to use an excel work sheet as a quick data entry interface
with the client. Once the client finishes the data entry, he uploads
the file to our web server. Then, the web server uses asp code to
process the excel file and updates our database.

So there can't be any manual steps. If I can't find a more powerful
driver, then I have to use a much less eligant hack on the excel file.
:-(

- Clark



"pk" wrote in message ...
This may or may not be helpful - my experience:

A built-in limitation of ADO is that it can only pull in
one data type from any given column. It samples the first
few rows and whichever data type is prevalent wins. The
driver then only retrieves that data type from that
column.

So for example, if you have text in your first eight or
ten rows and 30,000 values beneath that, only the text
will be retrieved.

The only solution is to be sure that none of your
critical columns contains mixed types.

I worked around this by:

1. Turn off screen updating
2. Open the source file conventionally - read only
3. Copy the data of choice
4. Close the file without saving changes

This may seem less punchy and cutting edge but, it ALWAYS
works and it's less code intensive.

I have also found ADO to be unstable, due to its driver
dependency. Sometimes it works, then you get new drivers,
and then it quits again. I got tired of supporting it and
switched practically all my code over.

Hope this helps somehow...


-----Original Message-----

I'm currently using the Microsoft ADO excel driver, and
I'm having difficulty setting the data type when I
retrieve the data from file. The automatic type
detecting
feature on the ADO driver is not doing a good job.

Any one know a good driver for reading data from an MS
Excel file? Suggestions please.




.

Check the following registry settings for the machine:

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.

--

(Clark Li) wrote in message . com...
Thank PK, your comment is good.

However, the situation at my company does not allow this solution. We
are trying to use an excel work sheet as a quick data entry interface
with the client. Once the client finishes the data entry, he uploads
the file to our web server. Then, the web server uses asp code to
process the excel file and updates our database.

So there can't be any manual steps. If I can't find a more powerful
driver, then I have to use a much less eligant hack on the excel file.
:-(

- Clark



"pk" wrote in message ...
This may or may not be helpful - my experience:

A built-in limitation of ADO is that it can only pull in
one data type from any given column. It samples the first
few rows and whichever data type is prevalent wins. The
driver then only retrieves that data type from that
column.

So for example, if you have text in your first eight or
ten rows and 30,000 values beneath that, only the text
will be retrieved.

The only solution is to be sure that none of your
critical columns contains mixed types.

I worked around this by:

1. Turn off screen updating
2. Open the source file conventionally - read only
3. Copy the data of choice
4. Close the file without saving changes

This may seem less punchy and cutting edge but, it ALWAYS
works and it's less code intensive.

I have also found ADO to be unstable, due to its driver
dependency. Sometimes it works, then you get new drivers,
and then it quits again. I got tired of supporting it and
switched practically all my code over.

Hope this helps somehow...


-----Original Message-----

I'm currently using the Microsoft ADO excel driver, and
I'm having difficulty setting the data type when I
retrieve the data from file. The automatic type
detecting
feature on the ADO driver is not doing a good job.

Any one know a good driver for reading data from an MS
Excel file? Suggestions please.




.