Aneel Ismaily

I am a DBA and a beginner with VB scripting. i have a scenario where i need a vb scripting guru help. i have a DTS package where i have a code which reads a text file and then prvide the value in a double format. code is like this;

netAmount = CDbl(Mid(sLine,93,12))/100.0

where sLine is basically the line in the text file and usually the numbers starting from character 93 and goes upto 12 characters looks like this, "000000123450"

Some times we have negative numbers in the file as well and they are definied like this;

} = 0-
J = 1-
K = 2-
L = 3-
M = 4-
N = 5-
O = 6-
P = 7-
Q = 8-
R = 9-

So if you encounter 12345} you would translate that to 123450-

when ever we encounter the negative number in file it fails i need some one to help me with how to tackle this task. i need some one to code me to convert }-R into a real number so the computation doesn't fail and package runs successfully.


Re: VB Script Help

Peter Mo.


The easy way is to create your own function. Your code could then look something like ...

netAmount = ourCDbl(Mid(sLine,93,12))/100.0

and the function could be ...

Code Snippet

Function ourCDbl(arg As String) As Double

Dim lastChr As String

If IsNumeric(arg) Then
ourCDbl = CDbl(arg)
Exit Function
End If

lastChr = Right(arg, 1)

If lastChr >= "J" And lastChr <= "R" Then
ourCDbl = -CDbl(Left(arg, Len(arg) - 1) & Chr(Asc(lastChr) - 25))
ElseIf lastChr = "}" Then
ourCDbl = -CDbl(Left(arg, Len(arg) - 1) & "0")
MsgBox "Last char not recognised " & arg
ourCDbl = 0#
End If

End Function

See how you get on.


Peter Mo.

Re: VB Script Help

Aneel Ismaily

thank you so much, i will try it and then share my feedback with you