ADO SQL type cast Float to String (Excel)

Question

How do I use the CAST in ADO to convert Float values to String?

I tried

SELECT CAST([Field] AS VARCHAR(20)) FROM ...

and

SELECT CAST([Field] AS STRING) FROM ...

and always get an OLE Exception (Unknown error).

The table column contains mixed numeric (right justified) and alphanumeric (left justified) values. If there are only alphanumeric values, the ADO query field type is String.

I am using Delphi 2009 ADO and Excel 2010.

Answer 1

CAST is SQL-Server expression. use SELECT Field FROM...

in delphi: ADOQuery.FieldByName('Field').AsString

you cannot cast it via SQL statement.

when using mixed data types:

Read this from MSDN (A Caution about Mixed Data Types):

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.

you will need to add IMEX=1 in the Extended Properties section of the connection string. the persistent field will be of TWideStringField.

The connection string should look something like this:

Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=C:\MyFile.xls;Mode=Share Deny None;Extended Properties="Excel 8.0;IMEX=1";...

Answer 2

Perhaps using CStr would work, ie

SELECT CStr([Field]) FROM...