Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2780
  • Last Modified:

Convert Exponential values in SSIS

Hi

I am trying to import datafrom a CSV File.. I have few Column hich have exponential Data (2.35E+11) something like that .. How do i convert so that i get the Numeric output i.e., 23500000000.. I have tried using Data Conversion task and convert that colum to DT_R4 single precision and DT_R8 - Double Precision but no luck..

Any Suggestions...
0
danny1620
Asked:
danny1620
  • 4
  • 3
2 Solutions
 
MNelson831Commented:
Not sure how much this will help, but I just ran this SQL Statement:

select convert(decimal,'2.35E+11')

and received an error converting varchar to decimal, but when I ran it again without the quotes:

select convert(decimal,2.35E+11)

it returned 235000000000
0
 
reb73Commented:
Try bringing the exponential values in as character data (DT_STR) and then convert this to a float using TSQL..
0
 
danny1620Author Commented:
I Tried to convert the Column values to decimal as you said but i get the following error.. This is the same error when I try to convert it to DT_R4 or DT_R8
[Data Conversion [756]] Error: Data conversion failed while converting column "Column 15" (165) to column "Copy of Column 15" (772).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
[Data Conversion [756]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "output column "Copy of Column 15" (772)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Copy of Column 15" (772)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

 
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
MNelson831Commented:
Using Reb's idea, I ran this successfully:

select convert(decimal,convert(float,'2.35E+11'))
0
 
danny1620Author Commented:
Thanks guys that worked ... i actually had to use  a staging table to get the data into it and then use Convert to get that into actualt data ..Now.. I have two more problems with the data i have ..
Source is a CSV but i have Few Columns were the Value has a , in it Such as South Korea,republic Of  ..Is there any way that i check that and avoid creating an extra column and the Datetime values in the source are as 2009-02-09-14.26.38.000000 instead of
2009-02-09 14.26.38.000000
So I tried using the below expression in the derived column to convert the datetime but get an error saying otential Loss of Data
SUBSTRING("[Column 16]",1,10) + " "+SUBSTRING("[Column 16]", 12,30)
Thanks
0
 
MNelson831Commented:
Select
     Case
         When charindex(',',[Column 16]) > o then right([Column 16], len([Column 16]) - Charindex([Column 16])) + left([Column 16],Charindex([Column 16] -1)
          else [Column16]
    End as [Column 16]
   
0
 
MNelson831Commented:
Oops

This was supposed to >0 not >o
 When charindex(',',[Column 16]) > o
0
 
danny1620Author Commented:
Hi Nelson..
Thanks but the Select Statement does not work .. gives me an error saying CharIndex requires to 2 or 3 arguments ..
My expression below works
SUBSTRING([Column 16],1,10) + " " + SUBSTRING([Column 16],12,2) + REPLACE(SUBSTRING([Column 16],14,6),".",":") + SUBSTRING([Column 16],20,4)
But i want to make it a little better .. I mean dynamic .. appreciate your help
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now