ADO SQL type cast from Float to String (Excel)

Question

How to use 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).

A column of the table contains mixed numeric (legal) and alphanumeric (left) values. If there are only alphanumeric values, the ADO request field type is String.

I am using Delphi 2009 ADO and Excel 2010.

Answer 1

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

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

you cannot use it with an SQL statement.

when using mixed data types :

Read this from the MSDN (Mixed Data Type Warning):

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

you need to add IMEX=1 in the Advanced Properties section of the connection string. the constant field will have a 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

Maybe using CStr will work, i.e.

 SELECT CStr([Field]) FROM... 

Answer 3

Try

 SELECT IIF([Field] IS NULL, NULL, CSTR([Field])) AS [Field] FROM ...