data problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

data problem

Author

Topic

skumar
Starting Member

10 Posts

Posted - 2006-09-25 : 11:34:27

Hi Guys,

i have a text file which i import to sql server table through DTS.
everything is fine except the negative data value in the text file which gets transfered to the table in the same format.

negative data value in text value 1.20e2 which should be -0.0123

now this could be fixed if i put one step in the middle which is take the text file data to excel and convert the 1.20e2 (which is scientific) to -0.0123 (as value) but the text file is huge and we don't want the extra set...

can we do anything in SQL for this... please advice

thanx in advance

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-25 : 11:41:57

Why does text value 1.20e2 become -0.0123? It looks to me like it should become 120.0.

CODO ERGO SUM

skumar
Starting Member

10 Posts

Posted - 2006-09-25 : 11:47:43

My bad, text value lets say is 8.96E-02 (in the text file) and i want it to be 0.0896 in sql table..

Again, Sorry for the confusion...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-25 : 11:57:26

How do you have the column that you are importing into defined?

This shows a conversion from a text scientific format to numeric.

select Num=convert(numeric(10,4),convert(float,'8.96E-02'))

Result:

Num          
------------ 
.0896

(1 row(s) affected)

CODO ERGO SUM

skumar
Starting Member

10 Posts

Posted - 2006-09-25 : 12:13:17

Hi Micheal,

thanx for the quick turn around....

please pardon me as i'm really new in this, but anywhere i can read more abt. this seems to be an opeartion once the data is transfered from text to sql table..

i think i need to put a variable for the 8.96E-02.. please explain a bit further...

thanx in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources