PDA

View Full Version : Extracting directly from a text file into excel with specific conditions in theheader



yazzy10
02-08-2019, 03:29 AM
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
02-10-2019, 08:51 PM
Hi yazzy10!
Please refer to the Attachment.

yazzy10
02-11-2019, 05:12 AM
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
02-11-2019, 03:42 PM
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