Error converting varchar to real

[theomen]

Hi,

I'm currently having a problem with an application I'm writing that imports data from a number of sources (Excel spreadsheets), then performs calculations on this imported data.

The problem I'm having is that the code I'm using to import the data into a sql table (which is automatically created by this code) is setting fields that should contain Real values to type nvarchar. This means that when I try to put the value into a field of type Real it breaks. The trouble is, I can't put it into a field of type varchar as then I can't perform calculations on the data. I've tried using "convert(real, value)" but it just says that it can't convert from varchar to real.

Can anyone suggest a workaround for this?

For information, the code I'm using to import the spreadsheet into the temporary table is:

SET @strSQL = 'SELECT * INTO [' + @target + '] FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=' + @filename + ';HDR=YES;IMEX=1'', ''SELECT * FROM [' + @worksheet + '$]'')'

exec sp_executesql @strSQL

 

[vongrunt]

The trouble is, if value contains 'blah' and you are expecting rean number - calculations on the data are not possible anyway. Pick your poison :>

Sometimes it is possible to replace bad values with 0, NULL or whatever. Is that true in this case? If not, then data is missing and generally speaking problem is not yours - send spreadsheets back to author(s) if you have The Power (tm) to do that.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[theomen]

Thanks for the reply.

I've actually figured out a (messy) workaround for the problem, where I take the value to pieces (so take off the minus sign to turn it to a positive value), which then allows me to put it into a Real variable. Then, I perform a calculation to convert it back to its original negative value.

It works, for now.