Extracting directly from a text file into excel with specific conditions in theheader

[yazzy10]

Hi

I am new to VBA and i require some help to run through a large text file to get specific data from the file.

I am looking to extract the data with the following headers BUT where Account read method should only = ESTIMATED


Name pinnumber,Manufacturer Job Serial Number,Account End Value,Account Entry Date,Rollover Flag,Account Read Type,Account Read Method,ID


Are you able to write me a code which I can use?any help would be really appreciated.

Thanks in advance.


Example of the test file



Name pinnumber,Manufacturer Job Serial Number,Account End Value,Account Entry Date,Rollover Flag,Account Read Type,Account Read Method,ID510PR,2133355,72148,2017-04-01,0,G,ESTIMATED,3005482731S1X

[大灰狼1976]

Hi yazzy10!
Please refer to the Attachment.

[yazzy10]

Thank you very much for your help. The code works really well for the test sample but when i apply the code to the much larger text file(300mb size), EXCEL is crashing.is there anything i need to change for it to execute smoothly?thanks again

[Paul_Hossler]

With that much data, it's probably better to read the file line by line from disc

Option Explicit
Sub Extract()
    Dim ws As Worksheet
    Dim iFile As Long
    Dim sFile As String, sLine As String
    Dim iOut As Long
    Dim v As Variant
    
    'get file name
    sFile = Application.GetOpenFilename
    If sFile = "False" Then Exit Sub
    
    'setup
    Application.ScreenUpdating = False
    Set ws = Worksheets("Sheet1")       '   change to suit
    ws.Cells(1, 1).CurrentRegion.EntireColumn.Delete
    iOut = 1
    
    iFile = FreeFile
    
    Open sFile For Input As #iFile
    
    'The Line Input # statement reads from a file one character at a time until it encounters a carriage return (Chr(13))
    'or carriage return-linefeed (Chr(13) + Chr(10)) sequence. Carriage return-linefeed sequences are skipped rather than
    'appended to the character string.
    'read headers
    Line Input #iFile, sLine
    v = Split(sLine, ",")
    ws.Cells(iOut, 1).Resize(1, UBound(v) + 1).Value = v
    iOut = iOut + 1

    Do While Not EOF(iFile)
    
        'read rest of file
        Line Input #iFile, sLine
        
        If Len(Trim(sLine)) = 0 Then GoTo GetNextOne
        v = Split(sLine, ",")
        
        If v(6) <> "ESTIMATED" Then GoTo GetNextOne
        
        ws.Cells(iOut, 1).Resize(1, UBound(v) + 1).Value = v
        iOut = iOut + 1
GetNextOne:
    Loop
    Close #iFile
    
    ws.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
    
    Application.ScreenUpdating = False
    
    MsgBox "done"
End Sub