Start reading text file based on line number

[jadams0173]

I'm tring to read a text file from a point where I left off. I know the line number where I stopped and would like to start reading again after that line. Is this possible. I've been searching but I guess I'm not using the right criteria to find what I'm looking for.

Here is what I have.

(The reason to copy the text file to a table, is not everyone has permissions to the folder. Also this is a production text file and should someone open or delete it bad things could happen. The text file is generated by some test equipment. A software upgrade when from using a TAB as the delimiter to a SPACE, which is why I do the replace. There is also lines that are of no use to the user so I delete them. The table is used to run queries off of other tables.)

Function ReadTextFile()
Dim fso         As FileSystemObject
Dim STREAM      As TextStream
Dim CurLine     As String
Dim arrValues() As String
Dim db          As DAO.Database
Dim rs          As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblTextFileImport", dbOpenDynaset)

Set fso = New FileSystemObject

'OPEN THE TEXT FILE
Set STREAM = fso.OpenTextFile("F:\scanfile.txt")

'LOOP THRU THE TEXT FILE ONE LINE AT A TIME
While Not STREAM.AtEndOfStream

'READ THE LINE
CurLine = STREAM.ReadLine

'REPLACE THE {TAB} WITH A {SPACE}
CurLine = Replace(CurLine, vbTab, Chr$(32), , , vbTextCompare)

'SPLIT THE LINE BY THE {SPACE} INTO THE ARRAY ARRVALUES
arrValues = Split(CurLine, " ")

'ADD THE CONTENTS OF THE TEXT FILE TO THE TABLE
    With rs
        .AddNew
            !id = Val(Trim(arrValues(0)))
            !SFCNUMBER = Trim(arrValues(1))
        .Update
    End With
    
Wend


'DELETE THE RECORDS THAT DO NOT CONTAIN MTX AS THE START OF THE SFC NUMBER
db.Execute "DELETE tblTextFileImport.SFCNUMBER " _
        & "FROM tblTextFileImport " _
        & "WHERE (((tblTextFileImport.SFCNUMBER) Not Like 'MTX*'))"
        
'REFRESHES THE RECORDSET SINCE WE DELETED RECORDS FROM IT
rs.Requery

'STORE THE LAST LINE NUMBER OF THE TEXT FILE IN THE LAS RECORD OF THE TABLE
    With rs
        .MoveFirst
        .MoveLast
        .Edit
          !LASTLINE = STREAM.Line - 1
        .Update
    End With
    
rs.Close
db.Close
Debug.Print STREAM.Line - 1
STREAM.Close
Set rs = Nothing
Set db = Nothing
Set STREAM = Nothing
Set fso = Nothing

End Function

 

[PWise]

Why are you reading in the textfile
did you try adding it to the mdb as a linked file

 

[jadams0173]

I considered that option, but was under the impression that if someone was using the link to run a query against it would lock the text file. Is this correct?

 

[abaldwin]

Also a linked file would not help you when, as you say, a user deletes the file.


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[jadams0173]

I think I'm on the path to solving the problem. I'm just not sure it's gonig to be faster than importing the entire txt file again as the only way I've found to search for my unique identifier is to read the entire text file again.

code snipet

Set fso = New FileSystemObject
Set STREAM1 = fso.OpenTextFile("F:\scanfile.txt")

TextFile = STREAM1.ReadAll

found = CDbl(Trim$(InStr(1, TextFile, FindID, vbDatabaseCompare)))
Debug.Print found
'--------------------------------------------------------------------
STREAM1.Close
Set STREAM1 = fso.OpenTextFile("F:\scanfile.txt", ForReading, False)

While Not STREAM1.AtEndOfStream
STREAM1.Skip (found - 1)
STREAM.SkipLine

 

[jadams0173]

oops...that should read

STREAM[red]1[/red].Skipline