Counting lines in text file

[davesmith202]

I have the following code to import a text file, with a counter to show the progress.

Public Function GetMyFile(pathToFile As String)
    Dim fileNum As Integer
    Dim myString As String
    Dim myTotalString As String
    fileNum = FreeFile

    Open pathToFile For Input As #fileNum

x = 0
    Do Until EOF(fileNum)
    x = x + 1
        Line Input #fileNum, myString
        myTotalString = myTotalString & myString & vbCrLf
        Me.txtCount1 = CStr(x) & "/"
        a = DoEvents()
    Loop

    Close #fileNum
    GetMyFile = myTotalString
End Function

This works fine, except I do not know how many lines it has to go through.

How can I find out the total number of lines in the text file, so I can then do...

Me.txtCount1 = CStr(x) & "/" & NoOfTextLines

Thanks,

Dave

 

[boblarson]

You would need to iterate through them first and then come back and do the import (so not a very efficient way of doing it, but you could do it).

 

[DCrake]

Open....   
Do Until EOF(fileNum)
       Line Input #Filenum, z
       rCnt = rCnt + 1
Loop
Close....

If you open the text file for the first time and simply perform the above to enumerate through the file to determine the numer of rows (rCnt).

Then repeat what you were doing opening the same file again, but this time you know how many lines there are in the text file.

You need to use the Line Input command line to get the bookmak to move to the next line in the file, what is passed to z is the line contents, but as at this point you are not concerned with it you just ignore it.

Now when you process the file for the second time you can use the rCnt value to show the user that you are processing row of rCnt. And if you want you can also express this as a percentage.

 

[davesmith202]

Liked the idea of a percentage and adopted that on another import process I have - thanks!

My text files can have over 100,000 lines to process, so import is fairly slow. Is there a faster way to import?

 

[boblarson]

My text files can have over 100,000 lines to process, so import is fairly slow. Is there a faster way to import?

I don't think so. I believe the TransferText method is only good up to about 65,000 records. After that you either need to split the file up to use it or do what you're doing (I had a similar issue once with even more records).

 

[davesmith202]

Ok, so its click Import and go make a cup of coffee time!

 

[DCrake]

Coffee? might think about a Bacon and Egg buttie as well.

 

[davesmith202]

Haha, too right there! Maybe an afternoon nap too.

 

[DJkarl]

This isn't by any chance a fixed width file is it? If so you could calculate the number of lines based off the record size and file size.

Just for fun I wrote something that seems pretty quick, at least faster then reading one line at a time. Still need to read in the whole file but does it pretty quick.

Function GetLineCountOfFile(fileName As String, Optional delim As String = vbCrLf) As Long
Dim fHDL As Double
Dim btAR() As Byte, stResult As Variant
ReDim btAR(1 To FileLen(fileName))
fHDL = FreeFile
Open fileName For Binary Access Read As fHDL
    Get fHDL, , btAR
Close fHDL
stResult = Split(StrConv(btAR, vbUnicode), delim)
GetLineCountOfFile = UBound(stResult) + 1
End Function