How to read the column which include text and number in the excel via ODBC?

Hi everyone,

Who can tell me how to read the column which include text and number in the excel via ODBC?
like one columnA include 'abcd'(Text) and 1234(number)
I want to read all the data in columnA, but seems that I can only read the data which the datatype is consistent with the begining of the data in the column.
For example. (please correct me if anything is wrong)
If the begining of the data are 'adcd', 'dfd-yrtd','yreh4645', the data type in this column was defined as text by default, and then I can't read the numeric data in this column if there is numeric (like 1123) data in the column.

I use the sql like: select * from ...
So I think I should read all the data in this column.


Thanks
Winnie

Use IsNumeric to test if the text can be converted to a number and Val to return the number.
This little demo shows how:

Please Login or Register  to view this content.

The only numbers output are 1, 0 and 1234.

Thanks for you reply.

I just want to query the data in the attachment by using the following quey in MSQuey. but seems that I can't read the numeric data which is "123" and "456". I think the begining of the row is string I input, right? If so, how to work around it?
testquey.xls
query:
SELECT `Sheet1$`.`No#`, `Sheet1$`.Name FROM `C:\testquey`.`Sheet1$` `Sheet1$`


Thanks
Winnie

Please read this MS Support article How To Use ADO with Excel Data from Visual Basic or VBA

In the section A Caution about Mixed Data Types
2 possible workarounds are given; either store numerics as text or use the setting "IMEX=1" in the connection string.

As a result, if your column contains mixed values, your only recourse is to store numeric values in that column as text, and to convert them back to numbers when needed in the client application by using the Visual Basic VAL function or an equivalent.

To work around this problem for read-only data, enable Import Mode by using the setting "IMEX=1" in the Extended Properties section of the connection string.

That is very useful info, Now I can read the data no matter which datatype it is. Thanks a lot ^_^