How to detect excess rows in txt or csv files before opening?

[39N 95W]

Windows XP Pro SP2
Excel 2002 SP3

I am trying to detect if a txt or csv file has more than 65,536 lines before
opening it. I've written this little tidbit for testing:

Sub testme()

Dim data As String
Dim fname As String
Dim c As Long

fname = "C:\911\_temp\test_data1.csv"

Open fname For Input As #1
c = 0
Do Until EOF(1)
Line Input #1, data
c = c + 1
Loop
Close #1

MsgBox c

End Sub


This works fine on files with less than or equal to 65,536 lines of data.
If I try the same code on a file with 65,537 lines or more then Excel locks
up.

Is there any way around this? Why is Excel crashing? Should I bite the
bullet and learn to use Access?

Any and all suggestions welcome. Thanks!

-gk-


========================================================================
"The creative act is not the province of remote oracles or rarefied
geniuses but a transparent process that is open to everyone."
-Greg Kot in Wilco Learning How To Die-

 

[Dave Peterson]

I ran that code against a giant text file that I have and changed the msgbox to
debug.print and got this back:

1,499,022

Those 1.5 million lines were read in pretty quickly.

Printing the time before and after showed:
13:36:35
1,499,022
13:36:36

(about a second)

====
Are you sure that it's this portion of the code that's causing the trouble?

 

[39N 95W]

I'm pretty sure because I set a series of test files. The test data is GPS
data, and I was able to create a test file with exactly 65,536 lines and
another with 65,537 lines. The first one executes just fine (and very
quickly as you noted). The second one seems to immediately go the hourglass
mouse pointer, and I can't even Alt|Page Break to get out of it.

The ultimate goal is to figure out a way to either cut these huge files into
multiple file sizes Excel can handle, or bring in the overflowing data into
sequential worksheets within a single Excel file.

Anyway, all I'm doing now is trying to detect files that will be too big for
Excel.

Maybe there's a better method?

-gk-

 

[Dave Peterson]

From Dana DeLouis:

Function NumberOfRecords(sFile As String) As Double
' = = = = = = = = = = = = = = = = = = = = = = = =
'// By: Dana DeLouis
'// Returns the number of lines in a text file
'// Use: Answer = NumberOfRecords("C:\Test.txt")
' = = = = = = = = = = = = = = = = = = = = = = = =
Dim f As Object
With CreateObject("Scripting.FileSystemObject")
On Error Resume Next
Set f = .OpenTextFile(sFile, 8) ' 8 -> Appending
If Err.Number > 0 Then
NumberOfRecords = 0
Else
NumberOfRecords = f.Line - 1
f.Close
Set f = Nothing
End If
End With
End Function

 

[39N 95W]

Thanks Dave!!

-gk-

From Dana DeLouis:

Function NumberOfRecords(sFile As String) As Double
' = = = = = = = = = = = = = = = = = = = = = = = =
'// By: Dana DeLouis
'// Returns the number of lines in a text file
'// Use: Answer = NumberOfRecords("C:\Test.txt")
' = = = = = = = = = = = = = = = = = = = = = = = =
Dim f As Object
With CreateObject("Scripting.FileSystemObject")
On Error Resume Next
Set f = .OpenTextFile(sFile, 8) ' 8 -> Appending
If Err.Number > 0 Then
NumberOfRecords = 0
Else
NumberOfRecords = f.Line - 1
f.Close
Set f = Nothing
End If
End With
End Function