VBA Parse a text file

salmec

New Member
Joined
Jan 24, 2013
Messages
2
Hi to all,
i've to parse a text file finding exactly some text in different lines.
For example:
file parse.txt
HATCH
5
A7122
330
309A6
100
AcDbEntity
8
QUOTE
62
1
100
AcDbHatch
10
0.0
20
0.0
30
HATCH
5
A7123
330
309A6
100
AcDbEntity
8
QUOTE
62
1 '<-I've to find this number
100
AcDbHatch
10
0.0
20
0.0
30
__________________
END OF FILE parse.txt

I would like to find the number after line with 62 (1) that is after this three lines
HATCH
5
A7123

I can read Lines from a txt file but i cant control the "next line parameter"
Start VBA CODE
Public Sub Parsedxf()

Dim sFileName As String
Dim iFileNum As Integer
Dim sBuf As String
Dim Fields As String
Dim TempStr As String
Dim strGenerator(0 To 3) As String
Dim i As Integer
i = 0

sFileName = "E:\Batch\parse.txt"
''//Does the file exist?
If Len(Dir$(sFileName)) = 0 Then
MsgBox ("Cannot find parse.txt")
End If

iFileNum = FreeFile()
Open sFileName For Input As iFileNum


Do While Not EOF(iFileNum)
Line Input #iFileNum, Fields
If Fields = "HATCH" Then

MsgBox (Fields)
End If
Loop
Close iFileNum
End Sub

End VBA CODE

The logic process that i would like to realize is:
Find "HASH"
if you find go to next line and find "5"
if you find go to next line and find "A7123" (if you dont find "A7123" then find "HASH" another time)
read the next line until reach "62"
go to the next line and manipulate "1" (for example replace 1 with a value in an excel cell)

Tips: the file that i've to parse have more than 6.000.000 rows so i have to find an efficient solution to manipulate le right row

Thanks in advance

Salmec
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Try this simple parser. It searches for the sequence HATCH - 5 - A7123 - 62. If that exact sequence is found it displays the line after 62 and resets the search to find the next sequence. If any of those strings are found out of sequence it resets the search to the beginning of the sequence (HATCH).

To modify the file contents, open a second file for output, read from the input file and write to the output file, modifying the found line (1 in your example) with the Excel cell value. At the end delete the original file using Kill and rename the second file using Name.

Code:
Public Sub Parsedxf()

    Dim sFileName As String
    Dim iFileNum As Integer
    Dim Fields As String
    Dim nextField As String
    
    sFileName = "E:\Batch\parse.txt" 
    
    iFileNum = FreeFile()
    Open sFileName For Input As iFileNum
    
    nextField = "HATCH"
    
    Do While Not EOF(iFileNum)
        Line Input #iFileNum, Fields
        Debug.Print Fields, nextField
        
        If Fields = "HATCH" Then
            If nextField = "HATCH" Then
                nextField = "5"
            End If
        ElseIf Fields = "5" Then
            If nextField = "5" Then
                nextField = "A7123"
            Else
                nextField = "HATCH"
            End If
        ElseIf Fields = "A7123" Then
            If nextField = "A7123" Then
                nextField = "62"
            Else
                nextField = "HATCH"
            End If
        ElseIf Fields = "62" Then
            If nextField = "62" Then
                nextField = "NEXT LINE"
            Else
                nextField = "HATCH"
            End If
        ElseIf nextField = "NEXT LINE" Then
            MsgBox Fields                   'display required line
            nextField = "HATCH"             'reset search
        End If
            
    Loop
    
    Close iFileNum
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top