Convert scientific notation back to numbers in SQL Server

Question

I had data in Excel like

7540006
7540447

But when I import the data into SQL Server, it is saved as

7.54001e+006
7.54045e+006

So now when I try to convert it to back to original state, it's not ending up with the correct value.

I have tried following queries for conversion

declare @a varchar(40)
set @a = '7.54001e+006'

declare @b decimal(27, 12)

SELECT @b = CONVERT(REAL, @a, 2)

SELECT LTRIM(RTRIM(str(@a))), LTRIM(STR(@a))

SELECT CAST('7.54001e+006' as REAL)

and the output I am getting is addition of 3 to original value for all methods i.e.

7540010
7540050

How do I convert it back to original state ??

Solution 1

Try the following query which gives the exact value

select CAST(CAST('7.54001e+006'  AS FLOAT) AS bigint)

Solution 2

All data is stored as Unicode string 255 (DT_WSTR) in excel.

Read excel data as Unicode form. then do conversion in ssis or database using.

SELECT CAST('7.54001e+006' as REAL)

In excel data source >> connection manager >>Data access mode == QUERY

     SELECT * FROM [SheetName$]

Comments

[S3S]

Agree with @Chuck, and I'd change that to TEXT in Excel if you are running into these formatting issues so it doesn't truncate your decimal into scientific notation. Then you can convert it

[Chuck]

It looks like it already lost the extra decimal places, so you will have to re-import it.

[rup]

Sorry for commenting on an old answer but i've been stuck on this problem and i'm wondering why does this solution work? - I was playing with numeric / real / text and they either were in scientific notation or rounded numbers, this worked beautifully but I was wondering why?