Post reply

Data type Convert problem between the SQL tables using the Derived Columns

  • April 12, 2010 at 4:39 am

    #156859

    Hi,

    I've two tables in SQL server and I'm extracting the data from the excel file. In the excel file there is one numeric column, it contains numeric values and "-" ("-" means "0"). So I'm extracting this to one SQL table and the data type of the particular field is varchar(10) because i need to insert the "-".

    After that I'm transferring that data into another SQL table using OLEDB source and OLEDB Destination with Derived columns. In the Destination table i've the same column name with float data type (need to insert 0 for "-").

    In the Derived columns, I used (DT_WSTR,20)Allowance == "-" ? 0 : Allowance, but it is giving the below error

    [OLE DB Source 1 [34]] Error: There was an error with output column "Allowance" (84) on output "OLE DB Source Output" (45). The column status returned was: "The value could not be converted because of a potential loss of data.".

    Please give me a idea to solve this.

    Thanks in Advance.

  • April 12, 2010 at 4:43 am

    #1149907

    hi,

    Write a select query with replace function for that particular column and remaining as it is....

  • April 12, 2010 at 4:47 am

    #1149909

    What is the data type of Allowance?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • April 12, 2010 at 5:07 am

    #1149915

    In Source SQL table it is varchar(10) and in Destination it is float.....

    Sasidhar... I'll try your idea and let you know... Thanks

  • April 12, 2010 at 5:11 am

    #1149916

    So ... the data type of your derived column must also be float?

    But you are setting the value of this column to Allowance, which is a string ...

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • April 12, 2010 at 5:19 am

    #1149924

    Yes.. the data type of the derived column is float.....

    In the expression... I convert the Allowance to string to check with the "-"...

  • April 12, 2010 at 5:28 am

    #1149929

    Are you saying that the data type of Allowance is numeric?

    That would suggest that "-" is a valid numeric entry - as far as I know, it is not.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • April 12, 2010 at 5:30 am

    #1149931

    Thanks Sasidhar...... It worked.......

    Is there any sql statement available to replace the all the special characters(ie, Other than Numeric) to "0"?

  • April 12, 2010 at 5:47 am

    #1149941

    Phil,

    This is my scenario...

    I've one excel file, in that file i've one column called Allowance which has only numeric values if it is 0 then the user will enter "-" for that...

    So for example the values like this... 12, 43, 13, -, 12..

    When I'm extracting the data in to First SQL table, I'm inserting all the data as it is in the excel, the datatype of the Allowance field is varchar(10) in the first table...

    After that I'm moving that data in to the second table. Before moving i've to convert the "-" to "0", because in the second table the data type of the Allowance field is Float.

    Thanks....

  • April 12, 2010 at 5:48 am

    #1149942

    hi,

    I think no such statement is availed....

    The alternative approach expecting is.....

    For Ur requirement u have to write a function which converts all special charectes to Ur required charecters.....

    Functuion: which takes String as Parameter

    Returns a string [which all special charectes are replaced with User requirement]

    🙂

    Thanks!

    Sasidhar Pulivarthi

  • April 12, 2010 at 5:55 am

    #1149944

    murugappan.kathiresan (4/12/2010)

    Phil,

    This is my scenario...

    I've one excel file, in that file i've one column called Allowance which has only numeric values if it is 0 then the user will enter "-" for that...

    So for example the values like this... 12, 43, 13, -, 12..

    When I'm extracting the data in to First SQL table, I'm inserting all the data as it is in the excel, the datatype of the Allowance field is varchar(10) in the first table...

    After that I'm moving that data in to the second table. Before moving i've to convert the "-" to "0", because in the second table the data type of the Allowance field is Float.

    Thanks....

    Aha, now I get you.

    You could use a derived column to get rid of non-numerics, but dumping them at source using REPLACE is more efficient & I'd stick with that. There is no standard wildcard replace, unfortunately, though you may find that someone has written one if you trawl the web.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • April 12, 2010 at 6:01 am

    #1149949

    OK.... Thanks.... :satisfied::laugh:

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply