vba - Text Reader class

[AshleyKitsune]

tldr version:

Trying to fix someone else's code who isn't here anymore. This hasn't been working for over a year and I have been tasked to fix it.

This is a class called cbTextReader, and is called to handle the lines of a text file for checking and manipulation. I've dug into what's happening when it's being used, and it appears to be reading the entire text file as one line of text, and so it instantly thinks it's reached the end of the file.

Option Compare Database
Option Explicit

Private FILE_HANDLE As Long
Private PREV_LINE_POS As Long
'

Private Sub Class_Terminate()
    If FILE_HANDLE > 0 Then
        CloseFile
    End If
End Sub

Public Function OpenFile(TextFileName As String) As Boolean
    FILE_HANDLE = FreeFile
    
    Open TextFileName For Input As #FILE_HANDLE
End Function

Public Sub CloseFile()
    Close #FILE_HANDLE
    
    FILE_HANDLE = 0
End Sub

Public Sub GoBack()
    Seek #FILE_HANDLE, PREV_LINE_POS
End Sub

Public Function GetNextLine() As String
    PREV_LINE_POS = Seek(FILE_HANDLE)
    Line Input #FILE_HANDLE, GetNextLine
End Function

Public Function PeekNextLine() As String
    PeekNextLine = Me.GetNextLine
    Me.GoBack
End Function

Public Property Get EndOfFile() As Boolean
    EndOfFile = EOF(FILE_HANDLE)
End Property

This is the snippet where it is being used:

    Dim textFile As cbTextReader
    Dim curLine As String     ' the current text line's text

    Set textFile = New cbTextReader
    
    textFile.OpenFile FilePRD
    
    Do While Not textFile.EndOfFile    ' Supposed to loop through the text file one line at a time
        curLine = textFile.GetNextLine               ' until it reaches the end of the file
        MsgBox curLine                           'me testing the values of the line and the value of the end of file method.
        MsgBox textFile.EndOfFile           ' curLine returned the entire string of the file instead of just one line
                                                             'EndOfFile returned True when I expected False since there are well over 100 lines in the file.

The person who created this moved on to bigger and better things, and I'm new here. I'm also the person in the office with the most experience with programming and vba in general, though it's mostly just dabbling whenever I get the chance.
I've looked into the Seek function and I'm wondering if perhaps it had an update that caused the class to work incorrectly. If anyone has any ideas on what I need to look into it would help me out. It took me forever to troubleshoot this database just to get to this point, and now I don't see why it's working the way that it is.
~Ashley

 

[GWteB]

It /used/ to work, but what I was told after I got here was that the main database from DISA went through an update and it stopped working, and since the guy who made the database isn't here anymore, no one could fix it (This is a common problem in our career field).

The cause of your issue is in front of you.
After the mentioned update, the data protocol has been changed.
The code you supplied expects CrLf or Cr delimited data (since the code used the Line Input statement) but apparently that's no longer the case here.
If the new protocol is known, the correct parser can be written for it.

​

 

[Micron]

To test that theory I'd create a new notepad file with several lines in it and test with that code.
Worked for me.

 

[GWteB]

@Micron, I've read the thread again, but I must be very much mistaken when I say, that the issue the OP is experiencing is that the data is not read line by line but in its entirety into a string variable.
She wants it per individual line. So the data file she's facing is not similar with a regular notepad file.

 

[AshleyKitsune]

@Micron, I've read the thread again, but I must be very much mistaken when I say, that the issue the OP is experiencing is that the data is not read line by line but in its entirety into a string variable.
She wants it per individual line. So the data file she's facing is not similar with a regular notepad file.

Yes you're right, I need to check each line individually, but the function isn't doing that. Here's an example of what the report might look like

HEADER DATA BLAHBLABLAH
Field names would go on this line, get skipped because it does not meet any critera.
AF123 21312 112    02        100000          2 213123456         DISCREPANCY VERBIAGE HERE (This line is a PRD line)
                             100000                      001 001 CORRECTIVE ACTION HERE (This is a corrective action line)
                                                                 06
AF123 21312 112    02        120000          2 213123457         DISCREPANCY VERBIAGE HERE (This line is a PRD line)
                                                                 CORRECTIVE ACTION HERE (This is a corrective action line)

How can I test what line return character the report is using?
~Ashley

 

[GWteB]

Did you open the file involved with Notepad?

 

[AshleyKitsune]

Did you open the file involved with Notepad?

Yes, when we download it it's saved as a text file. If a file ends in .TXT rather than .txt - that wouldn't change anything would it?

 

[AshleyKitsune]

I just tested the following code on the text file itself, as well as a NEW text file that I created myself. When used on the PRD text file, it still read the entire file as one line. When used on my text file, it was able to discern the differing lines, so you must be on to something here.

Dim tempnum As Integer
tempnum = 0
Dim TextLine
'Dim Line As String
Open FilePRD For Input As #1 ' Open file.


Do While Not EOF(1) ' Loop until end of file.
    tempnum = tempnum + 1

    Line Input #1, TextLine ' Read line into variable.
    Debug.Print tempnum & " " & TextLine ' Print to the Immediate window.
Loop
Close #1 ' Close file.

The text file:
A line
B line
C line

Returned the line number, plus the string that is the line:
1 A line
2 B line
3 C line

 

[GWteB]

It's most likely the Lf character (Ascii 10 / 0Ahex) is used otherwise you would have seen completely contiguous text on your screen, without line breaks.
To determine that for sure you could use this code after reading your file:

    If InStr(1, TextLine, vbLf) > 0 Then MsgBox "LineFeed detected"

 

[jackd]

If you use the free utility Notepad++ and turn on the show all characters button. You will be shown the line termination character(s)

 

[Micron]

I also suspect line feed/wrap character issues. Notepad++ seems like a good option.
@AshleyKitsune; if for some reason you cannot install Notepad++ because of work related restrictions, consider uploading the file (or a version where private data is replaced with anything at all), assuming you can upload files here. I've never tried so I don't know for sure. Pasting text into a post would not be good enough.
No, there is no difference between .TXT, .Txt, .txt or any variation of capitalization. Perhaps the system wherever the file is originating from uses something other than decimal values of 13 and 10, or they are in the wrong order for Access. I'd suspect that if that were true, this shouldn't have worked before but who knows. Besides, that system might have changed the characters (i.e. their Ascii number) or their order, or the data is now really XML or RTF format. In the latter case, such a line will now start with <div> but you'll never see it in normal viewing. These are reasons why having a portion (if not the whole file) would really help to diagnose.