Solved: Text file extract

hi,

i have a 12000 page 35mb text file that i cannot import into excel. there are 2 fields that begin in the 14th character postion that i need to import into excel.
these 2 fields will most likely appear twice on a page.

for each row that begins with the word NTWK,
the field begins in the 14th character that is 6 characters in length.

for each row that begins with the word OWNER,
the field begins in the 14th character that is 4 characters in length.

these 2 fields will always be separated by 3 rows.


any thoughts? is it possible?



thanks
zach

can u please put some sample data?

Is the first line in the text file a header line?

hi,

i was incorrect about the column position
the word NTWK and OWNER begins in column 2. and the fields that i need to extract are in column 16.
the first line in the text file is not a header line
attached is a santized copy of my text file
thanks for your thoughts
zach

A sample of the file in txt format would be better.

I can't test with your file but this assumes Tab delimiters as in the attached sample.

[VBA]Sub GetText()
Dim InputData, sTxt, i As Long
Open "C:\test.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, InputData
sTxt = Split(InputData, vbTab)
If sTxt(1) = "NTWK" Or sTxt(1) = "OWNER" Then
i = i + 1
Cells(i, 1) = sTxt(15)
End If
Loop
Close #1 ' Close file.
End Sub[/VBA]

hi malcolm,
i couldn't get your code to work correctly with my data.
here's the txt format file.
i had originally tried to upload the txt file but was unable. i didn't think to zip the txt file...
can you please look at this file


thanks
zach

[vba]

Sub GetText()
Dim InputData, sTxt, i As Long
Open "C:\sample.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, InputData
If Mid(InputData, 2, 4) = "NTWK" Then
i = i + 1
Cells(i, 1) = Mid(InputData, 14, 4)
ElseIf Mid(InputData, 2, 5) = "OWNER" Then
i = i + 1
Cells(i, 1) = Mid(InputData, 14, 6)
End If
Loop
Close #1 ' Close file.
End Sub
[/vba]

Hi Zach,
Slight misundestasnding of "columns"
XLD's code works (of course!), but it appears from your posted sample that the data to be returned starts in columns 16 & 17 respectively, but I'm sure you can sort that out.
Regards
MD

XLD & MD

thanks for your guidance. this works! amazing stuff.

zach