PDA

View Full Version : Solved: Text file extract



vzachin
05-24-2007, 05:22 AM
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

Veekay2007
05-24-2007, 05:25 AM
can u please put some sample data?

Bob Phillips
05-24-2007, 05:47 AM
Is the first line in the text file a header line?

vzachin
05-25-2007, 07:21 PM
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

mdmackillop
05-26-2007, 12:49 AM
A sample of the file in txt format would be better.

mdmackillop
05-26-2007, 01:24 AM
I can't test with your file but this assumes Tab delimiters as in the attached sample.

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

vzachin
05-26-2007, 07:16 PM
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

Bob Phillips
05-27-2007, 12:48 AM
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

mdmackillop
05-27-2007, 01:53 AM
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

vzachin
05-27-2007, 06:01 PM
XLD & MD

thanks for your guidance. this works! amazing stuff.

zach