Type Conversion Failure When Importing txt File

[orange]

I have looked at that material in more detail and have created a function and a test routine. I am attaching below the Function Ebc_Ascii and the test routine TestEbc_ascii. Also attached is the output from the test procedure.

I have attached 2 jpgs of the LIRA 3 table (original import )-- note field5 was changed to Text
and LiraFinal that was created with the query at the bottom of this post. Field5 here is called Fld5 and is a number. One jpg shows the table layouts, the other shows the stored values.

'---------------------------------------------------------------------------------------
' Procedure : Ebc_Ascii
' Author    : Jack
' Date      : 29-10-2012
' Purpose   : To convert a  string in N9.2 format from mainframe (EBCDIC) to a signed number in ASCII.
'   The output is in a string/text format with negative numbers with a leading - (minus) sign.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs:  a string representing the "strange number" to be converted
' Dependency: N/A
'--------------------------------------------------------------------------
'
'To convert the zoned ASCII field which results from an EBCDIC to ASCII character
'translation to a leading sign numeric field, inspect the last digit in the field.
'If it's a "{" replace the last digit with a 0 and make the number positive.
'If it's an "A" replace the last digit with a 1 and make the number positive,
'if it's a "B" replace the last digit with a 2 and make the number positive, etc., etc.
'If the last digit is a "}" replace the last digit with a 0 and make the number negative.
'If it's a "J" replace the last digit with a 1 and make the number negative,
'if it's a "K" replace the last digit with a 2 and make the number negative, etc., etc.
'Follow these rules for all possible values.  You could do this with a look-up table
'or with IF or CASE statements.  Use whatever method suits you best for the language
'you are using.  In most cases you should put the sign immediately before the
'first digit in the field.  This is called a floating sign, and is what most
'PC programs expect.  For example, if your field is 6 bytes, the value -123
'should read "  -123" not "-  123".
'
'** N9.2
'
Function Ebc_Ascii(x As String) As Long
' Dim x As String
    Dim i As Integer
    Dim SignToUse As Long
    Dim N92 As Long
    Dim arrPos As String
    Dim arrNeg As String
    Dim arrToUse As String
    'x = "0065G" '"0057J"    'original test data
10  arrPos = "{ABCDEFGHI"    ' equates to +0123456789
20  arrNeg = "}JKLMNOPQR"    ' equates to -0123456789
30  arrToUse = "          "  ' temp string
40  On Error GoTo Ebc_Ascii_Error

50  If Right(x, 1) Like "[0-9]" Then
60      'Debug.Print x  'for debugging
70      Ebc_Ascii = x
80      Exit Function
90  End If
    'check for a negative or positive  number
100 If InStr(arrPos, Right(x, 1)) <> 0 Then
110     arrToUse = arrPos  'use the positive values
120     SignToUse = 1
130 Else
140     arrToUse = arrNeg  'use the negative values
150     SignToUse = -1
160 End If

170 For i = 1 To 10

180     If Right(x, 1) = Mid(arrToUse, i, 1) Then
            ' next lines for debugging
            ' Debug.Print Right(x, 1)
            ' Debug.Print "Found  " & i - 1 & "   " & Mid(arrToUse, i, 1)
190         N92 = SignToUse * (CDbl(Left(x, 7)) & i - 1)
200         Ebc_Ascii = N92
210         Debug.Print Format(N92, "00000000")
220         Exit For
230     End If
240 Next i
250 On Error GoTo 0
260 Exit Function

Ebc_Ascii_Error:

270 MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure Ebc_Ascii of Module AWF_Related"
End Function

'---------------------------------------------------------------------------------------
' Procedure : testEBC_Ascii
' Author    : Jack
' Date      : 29-10-2012
' Purpose   : Routine to test the EBC_Asii function
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Sub testEBC_Ascii()
      Dim i As Integer
      Dim x(4) As String
   On Error GoTo testEBC_Ascii_Error

10    x(0) = "666666666"
20    x(1) = "12323245L"
30    x(2) = "11111234C"
40    x(3) = "11111111{"
50    x(4) = "22222222}"
60    For i = 0 To 4
70    Debug.Print "Test string  " & x(i) & "  becomes "
80     Ebc_Ascii (x(i))
90    Next i

   On Error GoTo 0
   Exit Sub

testEBC_Ascii_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure testEBC_Ascii of Module AWF_Related"
End Sub

The test results are

Test string  666666666  becomes 
666666666
Test string  12323245L  becomes 
-12323243
Test string  11111234C  becomes 
11111233
Test string  11111111{  becomes 
11111110
Test string  22222222}  becomes 
-22222220

I did import your data.


You can move from the Lira 3 table to a LiraFinal table with the proper characters using a query such as

SELECT [LIRA 3].Field1
, [LIRA 3].Field2
, [LIRA 3].Field3
, [LIRA 3].Field4
, EbC_Ascii([field5]) AS Fld5
, [LIRA 3].field6
into liraFinal


FROM [LIRA 3];