text file to table using vba

[sinyce]

I have a fixed width text file looks like this:

gl0340.txt

01061 ASSETS - BANKS
---------------------------
001-999999-02 FIRST NATL BANK

103001 MTR 3JUN02 INTEREST MAY2002 555.55


00593 ASSETS - MORE BANKS
------------------------------------
001-888888-01 SECOND NATL BANK

103002 MTR 3JUN02 INTEREST MAY2002 666.66


1. I wrote the code to read this file into memory one line at a time

2. Trying to use the select case construct - to create variables

3. At the point where the select drops out of the loop append the variables to a table, which should look thus:

ACOD CNUM MDATE SDATE INTEREST
1061 999999 3JUN02 MAY2002 555.55
0593 888888 3JUN02 MAY2002 666.66

4. Code written thus far:

Public Sub Text()
Dim strLine As String
Dim w_acod, w_actitle As String
Dim wvar1, wvar2 As Long

Open "C:\dbproject\gl0340.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, strLine

wvar1 = Val(Mid(strLine, 2, 4))
wvar2 = Val(Mid(strLine, 7, 6))
wvar3 = Val(Mid(strLine, 5, 6))

Select Case wvar1
Case Is > 1
w_acod = Mid(strLine, 2, 4)

End Select

Select Case wvar2
Case Is > 100
w_cnum = Mid(strLine, 7, 6)

End Select

Select Case wvar3
Case Is > 100
w_mdate = Mid(strLine, 25, 6)


End Select


Loop

Close #1
End Sub

5. I'm new to Access VBA and not sure if the Select Case can be written more efficiently. And now that i have my variables how do I append them to a blank new table?

Any help / comment would be appreciated.

Sinyce

 

[allanon2]

Try this and take it from here.

Public Sub Text()
Dim strLine As String
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("table1", dbOpenDynaset)
Open "C:\My Documents\gl0340.txt" For Input As #1
Do While Not EOF(1)
    Line Input #1, strLine
    If IsNumeric(Mid(strLine, 6, 5)) Then
        rs!MDATE = Mid(strLine, 26, 7)
        rs!SDATE = Mid(strLine, 42, 7)
        rs!INTEREST = Val(Right(strLine, 6))
        rs.Update
    ElseIf IsNumeric(Mid(strLine, 8, 6)) Then
        rs!CNUM = Mid(strLine, 8, 6)
    ElseIf IsNumeric(Mid(strLine, 2, 4)) Then
        rs.AddNew
        rs!ACOD = Mid(strLine, 2, 4)
    End If
Loop
Close #1
End Sub

I created a table like so:
ACOD CNUM MDATE SDATE INTEREST
1061 999999 3JUN02 MAY2002 555.55
0593 888888 3JUN02 MAY2002 666.66

The first 4 fields are all text fields. You will have to play with making them date fields, etc. The last field is a double field with two decimal places. You will notice that there is no key here. If I was doing this in real life I would have set up a non intellegent autonumber key field at the very least.

Hope this helps.

 

[sinyce]

This works well until I hit a record that has multiple entries, then I get a error 3020.

the record looks thus:

02293 LIABILITY - PRV CUSTOMER
------------------------------------
001-222222-01 GREENSTREET PROJECT

555555 SFN 3JUN02 INTEREST MAY2002 801.88
555556 PFG 6JUN02 INTEREST MAY2002 802.88
555557 MSG 7JUN02 INTEREST MAY2002 803.88

In this case the ACOD and CNUM remains the same and should look something like this:
ACOD CNUM MDATE SDATE INTEREST
2293 222222 3JUN02 MAY2002 801.88
2293 222222 6JUN02 MAY2002 802.88
2293 222222 7JUN02 MAY2002 803.88

Thanks again for your help.

 

[allanon2]

Ok. Try This One.

Public Sub Text()
Dim strLine As String
Dim strACOD As String
Dim strCNUM As String
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("table1", dbOpenDynaset)
Open "C:\My Documents\gl0340.txt" For Input As #1
Do While Not EOF(1)
    Line Input #1, strLine
    If IsNumeric(Mid(strLine, 6, 5)) Then
        rs.AddNew
        rs!ACOD = strACOD
        rs!CNUM = strCNUM
        rs!MDATE = Mid(strLine, 26, 7)
        rs!SDATE = Mid(strLine, 42, 7)
        rs!INTEREST = Val(Right(strLine, 6))
        rs.Update
    ElseIf IsNumeric(Mid(strLine, 8, 6)) Then
        strCNUM = Mid(strLine, 8, 6)
    ElseIf IsNumeric(Mid(strLine, 2, 4)) Then
        strACOD = Mid(strLine, 2, 4)
    End If
Loop
Close #1
End Sub

 

[sinyce]

Fantastic...this works wonderful. Thanks for your help.