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 ??

Answer 1

Try the following query which gives the exact value

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

Answer 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$]