Count rows in text file by loading into array
 

Hello --

The app can allow no more than 32,000 data points (1 column of numbers) to
be imported using a text file.

[1]
Is there a way to load the file into an array, count the records loaded, and
warn the user if 32,000 has been exceeded?

[2]
Then, if not exceeded, the app would then put the array values into a column
in a worksheet.

Can anyone help with ideas or sample code for doing [1] and [2]?

Thanks for any help.

Larry Mehl
mehl_at_cyvest.com

Count rows in text file by loading into array
 

Perhaps use the old I/O routines? ...
Open the file using 'Open'
Pre-read using Input# into InputString
i = 1
Do
cells(i,1).value = InputString
refresh InputString using Input#
i = i + 1
until EOF or i 32001
Close the file using 'Close'

"L Mehl" wrote in message
...
Hello --

The app can allow no more than 32,000 data points (1 column of numbers) to
be imported using a text file.

[1]
Is there a way to load the file into an array, count the records loaded,
and
warn the user if 32,000 has been exceeded?

[2]
Then, if not exceeded, the app would then put the array values into a
column
in a worksheet.

Can anyone help with ideas or sample code for doing [1] and [2]?

Thanks for any help.

Larry Mehl
mehl_at_cyvest.com

Count rows in text file by loading into array
 

Hi Larry,

See the following thread from last month: where a similar problem was posed:

http://tinyurl.com/5hjvo


---
Regards,
Norman




"L Mehl" wrote in message
...
Hello --

The app can allow no more than 32,000 data points (1 column of numbers) to
be imported using a text file.

[1]
Is there a way to load the file into an array, count the records loaded,
and
warn the user if 32,000 has been exceeded?

[2]
Then, if not exceeded, the app would then put the array values into a
column
in a worksheet.

Can anyone help with ideas or sample code for doing [1] and [2]?

Thanks for any help.

Larry Mehl
mehl_at_cyvest.com

Count rows in text file by loading into array
 

use a dataquery to import the text file.
then look at recordcount.
OR use this:



Sub ReadTextStuff()
'Note as is dimmed as string.
'thus any imported data will be imported AS TEXT

Dim h&, cnt&, data() As String
Const FileName = "d:\mytext.txt"
Const maxLines = 50

h = FreeFile
If Dir(FileName) < "" Then
ReDim data(1 To maxLines, 1 To 1)
Open FileName For Input As #h
While Not EOF(h) And cnt < maxLines
cnt = cnt + 1
Line Input #h, data(cnt, 1)
Wend

If Not EOF(h) Then
MsgBox "File is too long"
ElseIf vbYes = MsgBox( _
"Imported " & cnt & " items from " & FileName & _
vbNewLine & "dump in activesheet?", vbYesNo) Then
ActiveSheet.Cells(1).Resize(cnt) = data

End If
Close h
Else
MsgBox "File " & FileName & " not found", vbCritical
End If
End Sub

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


L Mehl wrote :

Hello --

The app can allow no more than 32,000 data points (1 column of
numbers) to be imported using a text file.

[1]
Is there a way to load the file into an array, count the records
loaded, and warn the user if 32,000 has been exceeded?

[2]
Then, if not exceeded, the app would then put the array values into a
column in a worksheet.

Can anyone help with ideas or sample code for doing [1] and [2]?

Thanks for any help.

Larry Mehl
mehl_at_cyvest.com

Count rows in text file by loading into array
 

Larry,

The following routine works for me:

Function DoLineCount(txtFilepath)
Dim FSO As Object
Dim objTextFile As Object
Dim fileText As String
Dim rowBound As Integer
Dim t As Integer
Dim fileTextArray
Dim newArray()

Set FSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = FSO.OpenTextFile(txtFilepath, 1)
fileText = objTextFile.readAll
fileTextArray = Split(fileText, vbCrLf)
rowBound = UBound(fileTextArray)

If rowBound + 1 32000 Then
MsgBox "WARNING: File length exceeds 32000 lines"
Exit Function
Else
ReDim newArray(rowBound, 0)
For t = 0 To rowBound
newArray(t, 0) = fileTextArray(t)
Next
ActiveWorkbook.Sheets(1).Range("A1").Resize(rowBou nd + 1, 1).Value =
newArray
End If

objTextFile.Close
Set objTextFile = Nothing
Set FSO = Nothing
End Function

The first part of the routine uses the FileSystemObject to read the text
into a variable, which is then converted to an array, fileTextArray, using
the Split function and vbCrLf. The number of lines in the file is
uBound(fileTextArray) + 1.

If the file passes the 32000 line test, a second array, newArray, is
created. Unfortunately, fileTextArray can't be written directly to the
worksheet (it's more like a list), so it's read into newArray. Once that's
done, though, the array can be passed to the worksheet in a single step.

Hope this helps,

Stan Scott
New York City

"L Mehl" wrote in message
...
Hello --

The app can allow no more than 32,000 data points (1 column of numbers) to
be imported using a text file.

[1]
Is there a way to load the file into an array, count the records loaded,
and
warn the user if 32,000 has been exceeded?

[2]
Then, if not exceeded, the app would then put the array values into a
column
in a worksheet.

Can anyone help with ideas or sample code for doing [1] and [2]?

Thanks for any help.

Larry Mehl
mehl_at_cyvest.com

Count rows in text file by loading into array
 

Thank you all for the suggestions.

We went with the array method.

Larry

"L Mehl" wrote in message
...
Hello --

The app can allow no more than 32,000 data points (1 column of numbers) to
be imported using a text file.

[1]
Is there a way to load the file into an array, count the records loaded,
and
warn the user if 32,000 has been exceeded?

[2]
Then, if not exceeded, the app would then put the array values into a
column
in a worksheet.

Can anyone help with ideas or sample code for doing [1] and [2]?

Thanks for any help.

Larry Mehl
mehl_at_cyvest.com