Parsing Data

  • Thank you for the solutions.  I used the Active X script suggested by David--added that to the script that was already running--it works great.  I have one more question with regard to that script.  I have another file to be parsed that is in the form of Lastname Firstname middle name (or Middle initial).  There is no comma to look for.  Is there a way to tweak that script so that it will look for the space instead of the comma--pretty much putting the data in the same columns as previously stated?

    Thanks for all of your help...


    Paula

  • The parsing worked great.  I was able to complete about 6 files.  I have another question with regard to the Active X script and parsing, though.  I have a single field with an entire name, including aka (in some of them).  I was thinking that I could use Active X script to parse this. 

    My initial attempt has me parsing out the "a/k/a" with the following script:

    '**********************************************************************

    '  Visual Basic Transformation Script

    '************************************************************************

    Function Main()

     Dim sIn

     Dim saka

     Dim swhole

     

      

     Dim sHold  ' Holding place while processing string

     

     Dim iLoc

     sIn = DTSSource("Whole_NAME")

     

     'look for a/k/a in input string,  first level format check

     ' if not found instr will return 0 or -1

     

     iLoc = instr(1,sIn,"a/k/a",0)

     if iLoc > 0 then

      

      'select the portion of the string before the location of the aka

      'this is the whole name

      'trim the leading and traiing spaces from the string

      swhole = trim(mid(sIn,1,iLoc -1))

      

      

      'load segment of the string after the aka into saka string

      'trim leading and trailing spaces

      saka = trim(mid(sIn,iLoc  -1))

      

      else

                 swhole = DTSSource("Whole_Name")

     saka="No AKA"

     

        

     end if

     

     DTSDestination("Whole_Name") = swhole

     DTSDestination("AKA") = saka

    Main = DTSTransformStat_OK

    End Function

    I then thought that I could do another transformation into another table to parse out AKA like this:

    '**********************************************************************

    '  Visual Basic Transformation Script

    '************************************************************************

    Function Main()

     Dim sIn

     Dim saka

     Dim swhole

       

     Dim sHold  ' Holding place while processing string

     

     Dim iLoc

     sIn = DTSSource("Whole_NAME")

     'look for AKA in input string,  first level format check

     ' if not found instr will return 0 or -1

     

     iLoc = instr(1,sIn,"AKA",0)

     if iLoc > 0 then

      

      'select the portion of the string before the location of the AKA

      'this is the whole name

      'trim the leading and traiing spaces from the string

      swhole = trim(mid(sIn,1,iLoc -1))

      

      'load segment of the string after the aka into saka string

      'trim leading and trailing spaces

      saka = trim(mid(sIn,iLoc  -1))

      

      else

                 swhole = DTSSource("Whole_Name")

     saka = DTSSource("AKA")

     

     end if

     

     DTSDestination("Whole_Name") = swhole

     DTSDestination("AKA") = saka

    Main = DTSTransformStat_OK

    End Function

    However, I got an error on this one.  I believe in the else saka=DTSSource("AKA").

    Is there a way to check for all of the possible aka combinations in just one transformation or is it better to step through multiple transformations?  If the second is the case, then why am I getting the error?

    Thanks,

    Paula


    Paula

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

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