Identify last row of record in a pipe-delimited text file

[Edmund]

I hv a pipe-delimited text file containing millions of records. This file is
automaticaly updated with more new records daily.

Is there a faster way to instantly identify the last row of record instantly
without using a counter to count the # of records in advance?

I'm unable to use random access as I don't know the last row # of the
database. Using a counter to count the # of records in advance is time
consuming. Wondering if there's sharper & faster ways achieve this.

Thanks in advance.

 

[SeanC UK]

Hi Edmund,

I am assuming that the file is open in Excel? If so, this should work:

Dim lngLastRow As Long
lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row

I hope this helps,

Sean.

 

[Edmund]

Excel 2003 is unable to open my pipe-delimited text file for it contains more
than 3,000,000 records with 12 fields.

I use the below procedure to return the answer but this is very time
consuming. Any faster & sharper method where we can immediately identify the
last record's row number?

Private Sub CountRecordsOutput()
Dim TextLine As String
Dim recCtr As Long

Open
"C:\PipeDelimitedTextFile_With3MillionRecordsAndStillGrowingDaily.txt" For
Input As #1

Do While Not EOF(1)
Line Input #1, TextLine
recCtr = recCtr + 1
Loop
Close #1
Debug.Print "Records : " & recCtr
End Sub

Thanks again.

 

[SeanC UK]

Hi Edmund,

You can open the text file in a different manner, which will still require
you to read line by line. Alternatively, you can open the text file as a
recordset, that way you can count the records like this:

Private Sub Text_File_RecordSet()
Dim connText As New ADODB.Connection
Dim rsTextRecordSet As New ADODB.Recordset
Dim strPath As String
strPath = "C:\"
connText.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath &
";Extended Properties='text;HDR=NO;FMT=Delimited'"
rsTextRecordSet.Open "Select * From PipeDelim.txt", connText, adOpenStatic,
adLockReadOnly, adCmdText
MsgBox (rsTextRecordSet.RecordCount)
If rsTextRecordSet.RecordCount < 65537 Then
Cells(1, 1).CopyFromRecordset rsTextRecordSet
Else
'TOO MUCH FOR EXCEL ROWS (2003 VERSION ANYWAY)
End If
connText.Close
End Sub

I have had to shorten your filename for the code to work. Also, you may need
to play with some of the connection settings. Once you have the Recordset you
can move through the records (each line/row in your file) and deal with it as
necessary. You can effectively work from the last record up and only place
the required records into your spreadsheet.

You will need to reference an additional library though, in the VBA editor,
go to Tools >> References and scroll down to one of the Microsoft ActiveX
Data Object libraries. I have used 2.7, although 2.8 is also available now.
These libraries should be there by default, although you can download the
files if needed.

I hope this is a faster option for you. I haven't tried with 3 million rows,
so I don't know if it is any quicker.

Cheers,

Sean.

 

[Edmund]

I've tried your recommendation using the ADODB procedure "Private Sub
Text_File_RecordSet()". In my machine, it took 18.4 seconds to count 469,560
rows in another sample text file. It worked absolutely fine but its speed
seems much slower. Testing with this same sample file of 469,560 records
using the former 'EOF loop & count' method took only 4.1 seconds in getting
its row count.

With this row count, I can use Random Access to zoom in & retrieve the
string of the last record of the text file.

Sean, TQ so very much for your assistance. I'm so glad u introduced me to
ADODB. That's really alien to me & is good stuff (for me) to start exploring
& learning from this day.