How to translate this Excel VBA code to an Access VBA code?

[shawnnnnnnn]

Hi all, I was tasked to create an Access application that will read through text files and store the lines into an Access table.

My buddy knew how to do it in excel, so he gave me this logic/code. As we are not trained in Access VBA, we are unable to translate this to something that Access can use.

Open file
Set arrayidx = 0
Initialize Array
While not EOF Do
    Read file
    If line contains 0~0~ES~
    {
        If arrayidx = 6
        {write array 1 to 6 into table}
        End If
    Initialize array
    Set arrayidx to 1
    }
    End If
    Move line to array
    Set arrayidx = arrayidx + 1
End While

[twinnyfo]

Shawn,

I think what you want to do is very doable. VBA is my language of choice, so a cant particularly help with a direct translation, but I am glad to assist with helping you with this.

First, I'm not sure how your friend got that code to work in Excel, as Excel also uses VBA, which that is not. If it had been in VBA, it would be almost a direct copy and paste into Access.

What are the contents of the text files? This is where you need to start? Once you understand the nature of the data, you can then build a table structure that will support the final data. Do you have any experience doing this?

I'll be glad to help you through the process. Need to know the data first.

[shawnnnnnnn]

Hi twinnyfo,

Really glad that you'll be able to guide me through this! I've tried several methods already, and they worked but with crazy errors. I'll post what I tried along with the codes later when I get home!

As for the data, it's basically a log file created by a system. It details every event that happened to the system for the month. Each event starts with the code "0~0~ES" and is detailed within 6 lines. If the event has more than 6 lines, it's considered faulty data and should be ignored.

E.g.
0~0~ES (normal event)
.
.
.
.
.
0~0~ES (Faulty event)
.
.
.
.
.
.
0~0~ES

Basically, this is the structure of the text file. There're no blank lines between each event, so the only way to identify each event is "0~0~ES". Also, each line of the event must be placed in a memo field as it waaaaaay surpasses the 255 limit of text. I'll upload a sample of the data later too!

[twinnyfo]

This is another post on your continuing conundrum? As I have seen some of the data in your previous threads, I think your focus needs to be on charting how to capture all possibilities within your text file first (not necessarily in code, but even just scribbling on a piece of paper or writing it down so you can share with us). For example, the standard record comes from six lines, but what happens that drives to a seventh or greater line? What are the NO KIDDINGLY record breaks? Are the record breaks always identical? What other possibilities are there for record breaks? What are the various fields you will be returning? Are these fields always identical? Yadda, yadda, yadda......

I will be glad to work through additional details.

[shawnnnnnnn]

I apologise for the multiple posts, I'm kind of in a desperate situation right now as this is the final key to my problem with the project due next Friday.

Give me about an hour, I'll try to compile a post with what I've tried so far, the error that's causing me a heck load of pain and my access database.

[shawnnnnnnn]

I managed to figure out how to import my text file into Access using recordset.

Private Sub Command0_Click()
    Dim LineCount As Integer
    Dim Line As String
    Dim strSQL As String
 
    Open "C:\Users\shawnntan\Downloads\oct\Oct2.txt" For Input As #1
 
    While Not EOF(1)
        LineCount = LineCount + 1
        Line Input #1, Line
        strSQL = "INSERT INTO Table2(ID, Field1) VALUES ('" & LineCount & "', '" & Line & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
    Wend
 
    Close #1
End Sub

The reason why I included the line number was because when I used Access's import function, it jumbled up the rows. However, I am still facing this problem now even though I included the line number.

I also have this code to remove the faulty data. It basically scans through the table, determines the start of an event and deletes those events with more than 6 lines.

Sub Del_Invalid(strSearch As String)
 
    Dim db As Database, rst As Recordset, lngcnt As Long, k As Long
 
    On Error GoTo Del_Invalid_Err
 
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Table2", dbOpenTable)
 
    lngcnt = 0
    With rst
 
        Do While Not .EOF
            Debug.Print !Field1
 
            If InStr(1, !Field1, strSearch) > 0 Then
                If lngcnt > 6 Then
                    'Delete offending records
                    .MovePrevious
                    For k = 1 To lngcnt
                        .Delete
                        .MovePrevious
                    Next
                    lngcnt = 0
                Else
                    lngcnt = 1
                End If
            Else
                lngcnt = lngcnt + 1
            End If
            .MoveNext
        Loop
        ' In case last one was offending
        If lngcnt > 6 Then
            'Delete offending records
            .MovePrevious
            For k = 1 To lngcnt
                .Delete
                .MovePrevious
            Next
 
        End If
 
    End With
 
Del_Invalid_Exit:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    Exit Sub
 
Del_Invalid_Err:
    MsgBox "Error " & Str$(Err.Number) & " = " & Err.Description
    Resume Del_Invalid_Exit
 
End Sub

Private Sub Command1_Click()
    Call Del_Invalid("ES~1~")
End Sub

I then have a code that will rearrange my table into 6 columns, with each event detailed in 1 record. The columns are set to memo.

Private Sub Command2_Click()
    Dim dbs As DAO.Database, rst As DAO.Recordset, rstInsert As DAO.Recordset, x As Long
 
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("Table2")
  Set rstInsert = dbs.OpenRecordset("Table1")
  If Not rst.EOF Then
    Do
      If x Mod 6 = 0 Then
        rstInsert.AddNew
        rstInsert![Field1] = rst![Field1]
      ElseIf x Mod 6 = 1 Then
        rstInsert![Field2] = rst![Field1]
      ElseIf x Mod 6 = 2 Then
        rstInsert![Field3] = rst![Field1]
      ElseIf x Mod 6 = 3 Then
        rstInsert![Field4] = rst![Field1]
      ElseIf x Mod 6 = 4 Then
        rstInsert![Field5] = rst![Field1]
      ElseIf x Mod 6 = 5 Then
        rstInsert![Field6] = rst![Field1]
        rstInsert.Update
      End If
      x = x + 1
      rst.MoveNext
    Loop Until rst.EOF
  End If
End Sub

The problem that I am facing now is that the rows of my data will end up being jumbled somehow. This causes my delete faulty data code to work wrongly, thus I'll end up with a really messed up table (not a multiple of 6). Which means I can't rearrange it like how I intended to. I've ran my delete code with many test cases using some with up to 10 lines, and it worked fine. So I'm guessing the problem lies with Access.

As for the data, I can't determine what causes the system to generate faulty data, but I can confirm that each line of data ends with a "~".

My Access file is too big to be uploaded. If you'd like to see it, maybe I can dropbox it to you?

[shawnnnnnnn]

Here's my Access file, if it helps.

[shawnnnnnnn]

When I import the file, some of the records get rearranged.
The records that are missing ended up at the bottom of the table.

When I use the sort from smallest to largest function on my ID field, it rearranges it properly. But my delete code made an error in the deletion.

It deleted records number 4028 to 4032. These are not supposed to be deleted as it is a normal event because this event consists of 6 lines from 4027 to 4032.

[shawnnnnnnn]

I just solved my problem using the FileSystemObject method. All's well now :)

[twinnyfo]

Glad you found a solution, although I'm not sure I assisted much with this one.....

:-)