Using VBA to read a certain line from a text file

Jaymond Flurrie

Well-known Member
Joined
Sep 22, 2008
Messages
868
So the situation is that I have a text file with a million lines. I need to read a certain line from there. How do I do it so that I don't read every single line from the beginning, but instead, say, line #431278 without reading the lines 1 to 432177 first?

The idea is that since the file is more than huge (over 4 GB), I need to avoid the massive delay that I expect to get if I read the lines in order like 1-1-2-1-2-3-1-2-3-4-1-2-3-4-5 (to get the five first ones) and so on would cause when I need to take a line, process it, pass it forward and take a new line.

Here's a code I use to read lines right now:

Code:
    sFileName = "C:\textfile.txt"

    If Len(Dir$(sFileName)) = 0 Then
        Exit Function
    End If

    iFileNum = FreeFile()
    Open sFileName For Input As iFileNum
    
    Do While Not EOF(iFileNum)
        Line Input #iFileNum, sBuf
        ' now you have the next line of the file in sBuf
        ' do something useful:
        vLine = Split(sBuf, Chr(9)) 'Split on tabs
    Loop
    ' close the file
    Close iFileNum

but that has the problem that when I get to any later lines, it most likely shoots the processing time thru the roof.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
Possibly Random Access may assist you:
http://support.microsoft.com/kb/150700


Neither Open for Input, nor Line Input use this retreival pattern:
1-1-2-1-2-3-1-2-3-4-1-2-3-4-5
They go straight through: 1-2-3-4-5.
(Line Input is actually collecting the bytes between the CR/LFs.)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,307
Messages
5,600,869
Members
414,411
Latest member
Snowmanaus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top