Results 1 to 3 of 3

VBA Parse a text file

This is a discussion on VBA Parse a text file within the Excel Questions forums, part of the Question Forums category; Hi to all, i've to parse a text file finding exactly some text in different lines. For example: file parse.txt ...

  1. #1
    New Member
    Join Date
    Jan 2013
    Posts
    2

    Default VBA Parse a text file

    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

  2. #2
    Board Regular
    Join Date
    Oct 2007
    Posts
    3,085

    Default Re: VBA Parse a text file

    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

  3. #3
    New Member
    Join Date
    Jan 2013
    Posts
    2

    Default Re: VBA Parse a text file

    Quote Originally Posted by John_w View Post
    Try this simple parser.
    [CUT...]
    Thanks John_w for you interest, a binary approach was discussed in this thread:
    https://groups.google.com/forum/#!se...o/4YC9LkoqukkJ

    And it works fast and fine.

    Regards

    Salmec

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com