Q:

"Error converting data type varchar to float" from a single line file import

I'm having trouble converting to float. Here is the code:
DECLARE @USRATE as varchar(10);

SELECT @USRATE = BulkColumn
FROM OPENROWSET(BULK 'file.txt', SINGLE_NCLOB) rate_from_file;

PRINT @USRATE;
PRINT CAST(@USRATE AS float);
PRINT CONVERT(FLOAT, @USRATE);

The txt file is simply a decimal value (i.e 0.8000 followed by carriage return) but both of the above commands returns:
0.7883
Msg 8114, Level 16, State 5, Line 9
Error converting data type varchar to float.

So both CAST and CONVERT don't work. I also tried converting to DECIMAL with the same output. Any idea how to fix this? 

A:

Hence, the @usrate has characters that are not recognized as valid digits.  It is quite possible that the end of line is interfering with the conversion.  You might try:
select cast(left(@usrate, 6) as float)

By the way, there is an argument that you should be converting to a decimal rather than a float, to avoid issues around floating point precision.

