Coloplast
asked on
Converting scientific "text" numbers into normal numbers
I have imported data from Excel into SQL.
When the data arrives in the SQL column it's scientific (E+)
My column in SQL is VARCHAR(20).
This is fine by me, but I need a script that will change the scientific to a normal number.
The formatting of the colum must stay as VARCHAR(20).
Hope you can help.
Regards
Rasmus Holt
When the data arrives in the SQL column it's scientific (E+)
My column in SQL is VARCHAR(20).
This is fine by me, but I need a script that will change the scientific to a normal number.
The formatting of the colum must stay as VARCHAR(20).
Hope you can help.
Regards
Rasmus Holt
ASKER
In Excel it's numbers.
I import like this:
TRUNCATE TABLE Vipcolo
INSERT INTO dbo.Vipcolo
SELECT * FROM OPENROWSET('Microsoft.ACE. OLEDB.12.0 ', 'Excel 12.0;Database=D:\DEAvayaRo uting\Vipc olo.xlsx', 'select * from [sheet1$]')
I import like this:
TRUNCATE TABLE Vipcolo
INSERT INTO dbo.Vipcolo
SELECT * FROM OPENROWSET('Microsoft.ACE.
select convert(varchar(20)
,convert(decimal(20,0)
,convert(float, '1.23E+09')
)
)
ASKER
Thanks.
I'm not an T-SQL expert, can you help with the rest of the code as well?
What do I put instead of '1.23E+09'
Shall I add a FROM as well?
Sorry :-)
I'm not an T-SQL expert, can you help with the rest of the code as well?
What do I put instead of '1.23E+09'
Shall I add a FROM as well?
Sorry :-)
ASKER
Think I got it:
select convert(varchar(20)
,convert(decimal(20,0)
,convert(float, VIP)
)
)
FROM Vipcolo
It seems to work as least :-)
select convert(varchar(20)
,convert(decimal(20,0)
,convert(float, VIP)
)
)
FROM Vipcolo
It seems to work as least :-)
ASKER
So how do I make the change to the column do I use UPDATE?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot.
How are you importing?
Using the wizard in SSMS? or an SSIS Package?