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 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.

Hi Ashley,

The code of this class reads line by line but the code in the additional snippet you supplied does that in a loop, so the storage of the returned text string is overwritten each time until the last line of text has been returned and the EndOfFile marker is reached.

The SEEK function just returns the current position of the pointer within the file data. This pointer increases after each read automatically and is supposed to point to the begin of the next text line. The SEEK function is used by the class's PeekNextLine function to look one text line ahead without affecting the file pointer; obviously this pointer will increase, but after the Peek the class's code sets the file pointer to its previous value. That's what the SEEK function is used for.

Apart from the lack of necessary security measures, the class's GetNextLine function only reads one line as intended.

 

[Micron]

While I admit that custom classes are my weakness, I'm not sure I agree with that assessment. The loop should run until the class returns EOF because the method of the class should run for each line in the file. textFile is an instance of the class and what its GetNextLine method should be returning is the next line in the file.
curLine = textFile.GetNextLine
and that should continue until EOF.
My guess is that the file you think it's opening isn't the one you think it is. Check to make sure that FilePRD is the file you think it is since you know how many lines you're looking at.

 

[james_lankford]

get rid of that class
that is absolutely no need for that whatsoever
the former programmer made a class solely for the purpose of making a class
just junk that piece of junk and start over
its reading a freaking file line by line for goodness sake
you say you're an experienced programmer
just write the code
if you can't figure it out google it
that is some god awful useless code right there

 

[Micron]

that is some god awful useless code right there

Maybe, or maybe you're not seeing the whole picture because it hasn't been revealed? Who knows, there could be code that parses the line and does something with delimited text but we haven't been shown that because it's not relevant to the problem. Not saying something like TransferText function wouldn't be better, just saying maybe there's a good reason that it exists. A class would certainly make it portable, which is something you really can't say for a standard module with procedures that are not readily exposed to other db's.

 

[jackd]

Ashley,
Can you provide more context?
Age of this application;
criticality to the organization;
is there an operational issue(s) with the code currently;
purpose of the application/database using this code;
any sample data in and expected out;
any info you have gleaned in your efforts to date.

As James says, it seems the class could be replaced with more common/standard vba code. But we may not be seeing the bigger picture as micron pointed out. It may help if readers had more info/requirements/documentation/sample reports/ operating procedures etc.

 

[AshleyKitsune]

Ashley,
Can you provide more context?
Age of this application;
criticality to the organization;
is there an operational issue(s) with the code currently;
purpose of the application/database using this code;
any sample data in and expected out;
any info you have gleaned in your efforts to date.

As James says, it seems the class could be replaced with more common/standard vba code. But we may not be seeing the bigger picture as micron pointed out. It may help if readers had more info/requirements/documentation/sample reports/ operating procedures etc.

I honestly don't know the answers to a lot of those questions! I work for the USAF, and normally we just run reports from the main database that is maintained by a third party (DISA I believe).

This specific database is a home grown solution that one person created in order to take those reports (which are not comma delimited, and difficult to import into excel) and do all the manual labor for you by reading the files, looking for lines that match a specific criteria, and then parsing them together when it finds lines that go together as records, and loading each record into the table for that specific report.
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).

Anyway, the office resorted to the old way of manually importing things, but they're begging me for help. I'll provide sample input & output in the next post, as well as the full set of code after I add comments into it. Thank you.
~Ashley

 

[jackd]

Many of us can empathize with your situation.
So on overview,
- there was an application that ran at a different site and maintained by an external group.
- the application your are "working on" is a custom extract and logic routine based on the remote database
- the extract processed report(s) for reformatting and use at your end
- the designer of the custom extract routine is no longer available
- nobody in your location has any details of the custom extract
- external group has changed "something" at their end
- the custom extract no longer works
- there is an impact on operations at your end

Consider:
- the format of the data at remote end has changed
- contact the creator(s) of the source report for info (data source/format/changes)
- can the external group(DISA) provide a report in the format you need?
- if your end(extract) hasn't changed, then most likely data/format change at source site

 

[AshleyKitsune]

Many of us can empathize with your situation.

Knowing how to do this stuff isn't a requirement for our career field. I just really love solving problems, and coding. It is a little challenging though since I have Dyslexia, and so I ask for help when I feel like I'm just missing something. Thanks for your help! One solution I found online to someone else who was experiencing this was to collect the lines using "Split(Input$(LOF(1), 1), vbLf)" and then process from there... I tested that option out and found that it will work as expected, unlike the following code. So at least there's a way forward.

Here's the full laydown of the product.
First, there are 6 different text reports that get imported into the database, each with their own sets of criteria. For THIS specific instance that I’m trying to fix, it is supposed to recognize one record as 3 potential separate lines. It uses functions called “IsPRD” “IsNarrative” and “IsCorrectiveAction” to check the line and see if it meets criteria, and returns true.

The function GetPRDsFromPRD uses those functions, and the sbTextReader class to check and compare the lines of the designated text file for lines that match, and when it has one or more matching lines, assembles them into a single record and adds it into an array to be returned outside of itself. Let me know if this still isn't enough.
~Ashley

Option Compare Database
Option Explicit

Public Sub ImportPRD(FilePRD As String, DataMonth As Date)  'This is the start of the process
    Dim PRDs() As PRDRecord          'Here we use defined functions to assemble a collection of PRD records
                                     'It is executed from a form where the user provides the first day of the month
    PRDs = GetPRDsFromPRD(FilePRD)   'As well as the location of the text file.
    
    If Not InsertPRDs(PRDs, DataMonth) Then 'If everything runs smoothly, you import the full list into the PRD table
        MsgBox "An error occurred while importing PRDs. Some data may be missing or not up to date.", vbCritical
    Else
        StoreFile FilePRD, sftPRD, DataMonth     'It usually doesn't get here, and instead displays the reject message above.
    End If
End Sub



Public Function GetPRDsFromPRD(FilePRD As String) As PRDRecord()
    Dim textFile As cbTextReader
    Dim curLine As String
    Dim prdBlock As String
    Dim PRDs() As PRDRecord

    
    Set textFile = New cbTextReader
    
    textFile.OpenFile FilePRD
    
    Do While Not textFile.EndOfFile     'Should loop until it reaches the end of thie text file
        curLine = textFile.GetNextLine  'Pulling the line for comparison (But pulls the whole file)
        'MsgBox curLine                 'This was my testing
        'MsgBox textFile.EndOfFile      ' to see what was going on
        'It returns the entire text file during the first iteration, and then stops.  Does not loop more than once.
        
        
        If IsPRD(curLine) Then   'Performs comparison. This line doesn't execute since the first line doesn't match a PRD line.
            MsgBox "reached second loop"      'It WILL execute once if you delete all of the reports header
            prdBlock = curLine                'information so that it starts with the matching PRD sequence.
                                              'As a result, a single record will be imported,
                                              'even when multiple lines match.
            
            Do While Not textFile.EndOfFile   'Second loop used to find second part of the record
                curLine = textFile.GetNextLine
                
                If IsNarrative(curLine) Or IsCorrectiveAction(curLine) Then 'Performs comparisons
                    prdBlock = prdBlock & vbNewLine & curLine
                    MsgBox "Reached third loop"              'I used this to determine if it would work with the header removed and it did.
                ElseIf IsCorrectiveAction(curLine, "###") Then
                    Exit Do
                ElseIf IsPRD(curLine) Then
                    textFile.GoBack
                    Exit Do
                End If
            Loop 'Exits the second loop
            
            If IsArrayEmpty(PRDs) Then       'Doesn't need explaining, typical array manipulation
                ReDim PRDs(0) As PRDRecord
            Else
                ReDim Preserve PRDs(LBound(PRDs) To UBound(PRDs) + 1) As PRDRecord
            End If
            
            Set PRDs(UBound(PRDs)) = BreakOutPRD(prdBlock)  'BreakOutPRD is a defined function to take
        End If                                              ' the strings collected to turn it officially into a record

    Loop
    
    textFile.CloseFile
    
    GetPRDsFromPRD = PRDs  'return the collection
End Function

 

[jackd]

Good stuff. I worked with a lady who was a technical programmer at IBM (years ago). She has dyslexia and worked in 360 assembler on system catalog routines. Very cryptic and a great area for typing errors.
I haven't looked at your code, but it you start with some sample data and work to get the output you need, I'm sure things will work. You can post questions if you have difficulty.
Google and youtube have lots of info. Steve Bishop on Youtube has excellent tutorials.