[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2628
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help 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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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