Reading through a large text file using VBA

Hi,

I want to read through a very large text file (having millions of records), copy the first 60,000 records in excel on sheet1 and do some processing. Once done, delete those 60,000 records from the text file which were copied in the previous step and then proceed to the next 60,000.
Continue doing the above until all the records in the large text file are processed.

Is it possible to achieve that using excel VBA? If so, any help will be appreciated.

Thanks,
Vikas

Vikas,

Welcome to the forum. While I'm not sure that VBA is the best for this kind of thing, it can definitely be done. For a file the size you're talking about, the macro may take some time to finish, but you should be able to use the following:

Sub ProcessLargeTextFileMacro_for_Vikas()
    
    Static txtPath As String: txtPath = Application.GetOpenFilename("Text Files, *.txt")
    If txtPath = "False" Then Exit Sub
    
    Static ws1 As Worksheet: Set ws1 = ActiveWorkbook.Sheets("Sheet1")
    Static ws2 As Worksheet: Set ws2 = ActiveWorkbook.Sheets("Sheet2")
    Dim txtLine() As Variant
    Dim i As Long: i = 0
    Dim strLine As String
    
    Application.ScreenUpdating = False
    Close #1
    Open txtPath For Input As #1
    While Not EOF(1)
        Line Input #1, strLine
        i = i + 1
        ReDim Preserve txtLine(1 To i)
        txtLine(i) = strLine
        If i = 60000 Or EOF(1) Then
            ws1.[A1].Resize(i, 1).Value = WorksheetFunction.Transpose(txtLine)
            
            ''''''''''''''''''''''''''''''''''''''''''''''
            '    Perform processing here                 '
            '    Extract processed information to ws2    '
            ''''''''''''''''''''''''''''''''''''''''''''''
            
            ws1.UsedRange.ClearContents
            i = 0
        End If
    Wend
    Close #1
    ws1.UsedRange.ClearContents
    Application.ScreenUpdating = True
    
End Sub

Hope that helps,
~tigeravatar

Thank you tigeravatar!!
That's exactly what I was looking for.

@Leith - The data in the file a single column data having http links. There will be one hyperlink on each row. The script which tigeravatar provided completely addresses my question here. I can make some changes to it as per my requirements and use it.

Thanks again for such a quick reply.