Number of lines

[Adrian T]

Hello,

I have an input in sequential format (semicolon delimited). Each record or
line is seperated by a carriage return and in the actual model, I could have
thousands of lines representing economic scenarios. My goal is to find out
the total number of lines before running any of calculations. Currently, my
code looks like this:

Dim strInputFileName As String
Dim strDataLine As String
Dim intFileNum As Integer

strInputFileName = <path>

intFileNum = FreeFile
Open strInputFileName For Input As intFileNum
While Not EOF(intFileNum)
Line Input #intFileNum, strDataLine
intScenario = intScenario + 1
Wend

Since my input/data is very very big, this process takes sometimes before I
get my total number of lines/scenarios (intScenario).

Do you know any better solution to find out how many lines/records/scenarios
in the file faster without using the while loop? I only care with the number
of lines, not the length of each line.

The closest function that's I could find from the help file is
LOF(intFileNum). But this looks like the number of characters in the file and
not the number of lines.

Please help and thanks very much,

Adrian T

 

[Tom Ogilvy]

if the lines are fixed length, then

length of file/length of line = #records

the length of a record would have to include the two characters CRLF

 

[Adrian T]

Hi Tom,

No, the lines aren't fixed length. They could be numbers and/or characters
that I can't control their length.

Do you have any other idea?

Thanks,

Adrian T

 

[stevebriz]

Here is a thought...this works on reading the file in and splitting it
on the VbCrlf into an array.
Dim strArray() As String
Erase strArray
Open strInputFileName For Input As #1
strArray = Split(Input(LOF(1), 1), vbCrLf)
MsgBox UBound(strArray)

 

[Adrian T]

Hi Stevebriz,

Thanks for the idea, I tried it out and it gave me the same amount of time
compared to my while loop. I guess there's not much I can do about it. But,
please let me know if you come accross with another thought.

Thanks,

Adrian T

 

[Tom Ogilvy]

split is a pretty slow function, perhaps


Dim cnt as Long, v
Open strInputFileName For Input As #1
v = Input(LOF(1), 1)
for i = 1 to len(v) - 1
if mid(v,i,2) = vbCRLF then
cnt = cnt + 1
end if
next
msgbox cnt + 1

 

[Adrian T]

Hi Tom,

I tried it and it was a little bit longer than Stevebiz's split or my while
loop. This is I think because each line has about 70k characters/digits. Yes,
I know it's a lot of data. We are projecting economic scenarios monthly for
the next 30 years (mortgages). Please let me know if you have another idea.

Thanks,
Adrian T

 

[stevebriz]

Just curious if this I found at:
http://home.att.net/~gobruen/progs/basic/index.html#txtcntln
is any faster.
You need to set the file paths.

txtFilepath = "c:\temp\test.dat"
txtOut = "C:\temp\out.txt"


Dim objFSO, objTextFilein, objTextFileout
Dim sRead, sReadLine, sReadAll
Dim LineCount


Const ForReading = 1, ForWriting = 2, ForAppending = 8

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFilein = objFSO.OpenTextFile(txtFilepath, ForReading)
Set objTextFileout = objFSO.CreateTextFile(txtOut, ForWriting)

LineCount = 1
sReadLine = objTextFilein.ReadLine

Do While Not objTextFilein.AtEndOfStream
sReadLine = objTextFilein.ReadLine
LineCount = LineCount + 1
Loop

objTextFileout.WriteLine (LineCount)
Set sReadLine = Nothing
objTextFilein.Close
MsgBox LineCount
End Sub

 

[Adrian T]

Hi Stevebriz,

I tried and it's slighty faster (not much though). For 1000 economic
scenarios (that's a lot of data), we're talking about less than 1-sec
difference. I think I am gonna go with your first suggestion. The split
function works perfectly for my purpose. Thanks so much for helping me out.

Thanks,
Adrian T

 

[NickHK]

Adrian,
Assuming this table is structured data, use a querytable with ;
SELECT COUNT(SomeColumn) FROM YourFile

I would imagine a DB approach would be optimised for this kind of access
compared to any form of looping/split etc.

NickHK