How to deal with an Amount Field that has space as a thousands separator AND a DR/CR indicator!

If you encounter an Amount field that has spaces as the thousands separator AND includes a DR/CR indicator (e.g. 12 345 678 D) then you'll need this to parse the value:

Dim strRawAmount As String = api.Parser.DelimitedParsedValues(4)   ' Zero-based and adjust for your columns!
'-----------------------------------------------------------------------------
' If not empty then remove space separator and interpret D/C indicators
If Not String.IsNullOrEmpty(strRawAmount) Then
    Dim strAmount As String = strRawAmount.Replace(" ", "")                             ' 12 345 678 D becomes 12345678D
    Dim strDRCR As String = Right(strAmount, 1)                                        ' D or C on end of string?
    Dim dAmount As Decimal = Left(strAmount, len(strAmount) - 1).XFConvertToDecimal    ' Strip D or C and convert to Decimal
    '-----------------------------------------------------------------------------
    ' Did conversion to Decimal work?
    If IsNumeric(dAmount) Then            ' This is critical
        '-----------------------------------------------------------------------------
        api.ValueIsnumeric = True        'This is critical too!!!
        Select Case strDRCR
            Case "C"    ' Credit
                Return (dAmount * -1).ToString
            Case "D"    ' Debit
                Return (dAmount).ToString
                '-----------------------------------------------------------------------------
        End Select
    End If
Else
    '-----------------------------------------------------------------------------
    ' Empty so bypass line
    globals.Bypass = True
    '-----------------------------------------------------------------------------
End If

 

 

Steve