Post reply

Import - Derived Column for data cleansing

  • July 30, 2009 at 5:56 pm

    #137545

    I have a flat file import of millions of rows. One of the columns contains data that may have a negative symbol at the end of the value to represent a negative number. During the import it errors trying to convert this into an integer. I have figured out the SQL to parse this when the vlaue has the negative symbol and correct the value. I was told to use a derived column object to implement this fix but I cannot figure out how to implement it using that object.

    EXAMPLE:

    Text File

    AcctNo, Balance

    1,200

    2,100

    3,300-

    4,500-

    5,600

    Table *Expected import values

    AcctNo(int) Balance(int)

    1______________200

    2______________100

    3______________-300

    4______________-500

    5______________600

    Any help would be greatly appreciated.

    Thanks.

    J.T.

  • July 31, 2009 at 1:21 am

    #1032807

    Hi

    Have you tried data conversion task? here you can convert source value to signed integer. You may need to update your destination column type as well.

    OR

    You can set up Error handling on your import task.

    You need to select "Redirect Row" in case of error and configure error flow to some other table where data type for the erroneous column can be varchar. Then later you can process this data seperately and move corrected data back to your table. After this process you should always truncate your error table.

    I hope this helps.

    Thank you.

    Rupashri

Viewing 2 posts - 1 through 1 (of 1 total)

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