real figure instead of exponential figure needed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

real figure instead of exponential figure needed

Author

Topic

ZenRoe
Starting Member

14 Posts

Posted - 2008-05-29 : 11:17:14

Hello,

I use OPENROWSET to read values from Excel and store them in a SQL Server table. In the Excel file I have a row having format 'Number' with two decimal places.

Example: 1225000.00

When I select this value using SSMS I get the correct value:

1225000

Strange enough, I cannot see the decimals anymore. However, when I now store this value into my table and then select it from there I get: (the datatype in the table is VARCHAR(max))

1.225e+006

I would not care if I could convert this back to a numeric datatype but this seems not to work: CAST('1.225e+006' as INT) throws an exception. Obviously OPENROWSET sends the data strictly as a character string. Storing this into varchar(max) works for small figures but it starts to use exp values for big figures.

Does anybody has an idea how to bring huge Excel based figures safely into a MS SQL Table ?

Thanks: Peter

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-29 : 11:50:19

Use this in your select statment from open row set

select convert(Float,mycol)

That should resolve the issue.

ZenRoe
Starting Member

14 Posts

Posted - 2008-05-29 : 12:26:22

Thanks for the quick answer. Still it does not work. Meanwhile I imported the Excel table into SQL Server using MS Access ....
A query against the imported table shows the correct figure but as soon as I insert it into the other table it gets converted. Obviously the problem is the destination datatype (varchar(max). SQL Server just converts those big figures into EXP. Is there any property or setting which would allow me to control this behaviour ????

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-30 : 00:00:16

It sounds like your value is coming from excel as a float. Use a decimal conversion.

Here's a example of how a decimal conversion with avoid expon.
[code]
Declare @OriginalVal varchar(50),@Val0 float, @val1 varchar(50), @Val2 decimal(20,2)
set @Originalval ='2132121321321.12'
set @Val1 = @OriginalVal
Select @Val1 as [Varchar]
set @Val0 = @Val1
Select @Val0 as [Float]
Set @Val1 = @Val0
Select @Val1 as [Varchar Bad Conversion]
set @Val1 = cast(@Val0 as decimal(15,2))
select @Val1 as [Varchar Proper Conversion]
[code]

ZenRoe
Starting Member

14 Posts

Posted - 2008-05-30 : 02:12:10

Thanks for all the answers which finally leaded me to the solution:

CAST(value as float)

did the trick.
Thanks !

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-05-30 : 06:52:50

You can also use below

cast(1.225e+006 as numeric(10,2)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources