March 24, 2005 at 5:44 am
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
April 4, 2005 at 8:26 am
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