Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify last row of record in a pipe-delimited text file
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. -- Edmund (Using Excel 2003) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify last row of record in a pipe-delimited text file
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. -- (please remember to click yes if replies you receive are helpful to you) "Edmund" wrote: 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. -- Edmund (Using Excel 2003) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify last row of record in a pipe-delimited text file
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_With3MillionRecordsAndSt illGrowingDaily.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. -- Edmund (Using Excel 2003) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify last row of record in a pipe-delimited text file
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. -- (please remember to click yes if replies you receive are helpful to you) "Edmund" wrote: 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_With3MillionRecordsAndSt illGrowingDaily.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. -- Edmund (Using Excel 2003) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify last row of record in a pipe-delimited text file
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. -- Edmund (Using Excel 2003) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pipe-Delimited CSv File | Excel Discussion (Misc queries) | |||
Adding a new file extension for Pipe Delimited files | Excel Programming | |||
Save CSV file as Pipe delimited text file | Excel Discussion (Misc queries) | |||
Import Pipe Delimited File, Parse out certian Fields, create new f | New Users to Excel | |||
Save file as text pipe delimited | Excel Programming |