Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2007
    Posts
    7

    Unanswered: DTS import Negative Excel values

    Hello everybody,

    I have a DTS package that imports excel spreadsheets.
    The excel spreadsheet cells contain numbers based on vlookups. Now negative values appear in these cells as -999 but when loaded into the table they appear as (999).
    Is there anyway way to configure it to just load -999? The tricky part is I don't have control over the format of the spreadsheet cell and the destination table field is varchar. I am hoping there is some way that the DTS interprets and picks up the value as it is displayed in excel.
    Or do I have to stick with converting it manually by replacing '(' and ')' with nothing and appending '-'?

    Thanks for any help.

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    you will have to alter the default transformation. edit the activex transformation as below

    Code:
    Function Main()
        '---for other columns
        DTSDestination("Col1") = DTSSource("Col1")
    
        '----for the column Col2 with (999) etc value
        if right(DTSSource("Col2"),1)=")" 
            DTSDestination("Col2") = "-" & DTSSource("Col2")
        else
            DTSDestination("Col2") =  DTSSource("Col2")
        end if
    
        Main = DTSTransformStat_OK
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •