ADO mixed data

[aviben]

Hello,

I am querying an Excel column (via VB6) column in excel of format General whose 7 first items are numerical and the next values are Text

The following works well but returns only the numerical values

ObjConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _ 
                       "Data Source=" & TempName & ";" & _ 
                       "Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"";" 
Sql = "SELECT * FROM " & rs1

'rs1 refers coorectly to the column address

Is IMEX=1 in the ObjConnection not intended to deal with mixed format?

Please help
Avi

[hamza.saleem]

You can always use the Format syntax to select or change the data format.

[aviben]

Could you explain a little more?

[hamza.saleem]

Sure, as we can take example of printing date so if we are printing date with this code

Label1.Caption = Date

It will print 7/19/2014
it is printing month/day/year and we want like day/month/year so we use "dd/mm/yyyy" in the format string like this

Label1.Caption = Format(Date, "dd/mm/yyyy")

now the output should be 19/7/2014

This is how you use to format string.

As per as your problem you want only numeric values from sql statement so you can use this:

 SELECT column1 FROM table WHERE Isnumeric(column1)

This will output only numeric values from the column and for furthur converting you can use format syntax above.

Edit :

MSDN Help Topic for printing numeric values from sql queries
http://social.msdn.microsoft.com/For...um=transactsql

[aviben]

Thanks...But actually I want the SQL statement to output both the numeric and string values from the mixed column formats

[dilettante]

IMEX isn't magic:

NOTE: Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric.

And if the sampling indicates a numeric type you get a numeric type. Any cells that can't be converted to numbers are returned as Null values.

[hamza.saleem]

Thanks...But actually I want the SQL statement to output both the numeric and string values from the mixed column formats

From sql statements i have not done this ever.I think it is not possible to retrieve data from different columns in a same recordset.

[westconn1]

from http://support.microsoft.com/kb/257819#RetrieveExcel

A Caution about Mixed Data Types

As stated previously, ADO must guess at the data type for each column in your Excel worksheet or range. (This is not affected by Excel cell formatting settings.) A serious problem can arise if you have numeric values mixed with text values in the same column. Both the Jet and the ODBC Provider return the data of the majority type, but return NULL (empty) values for the minority data type. If the two types are equally mixed in the column, the provider chooses numeric over text.

For example:

In your eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values.
In your eight (8) scanned rows, if the column contains three (3) numeric values and five (5) text values, the provider returns three (3) null values and five (5) text values.
In your eight (8) scanned rows, if the column contains four (4) numeric values and four (4) text values, the provider returns four (4) numbers and four (4) null values.

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. This enforces the ImportMixedTypes=Text registry setting. However, note that updates may give unexpected results in this mode. For additional information about this setting, click the article number below to view the article in the Microsoft Knowledge Base:
194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset

imex = 1 seems to work, as specified, on readonly recordsets

[aviben]

Thanks... What does mean ReadOnly recordset? Is there a way to make the recordset ReadOnly?

[westconn1]

a working example

Sql = "select * from[sheet5$] as s5"
rs2.Open Sql, cn, adOpenStatic, adLockReadOnly
Sheets("sheet1").Range("i1").CopyFromRecordset rs2