+ Reply to Thread
Results 1 to 9 of 9

Parsing a Text File

  1. #1

    Parsing a Text File

    Good Evening,

    I wonder if somebody could point me in the right direction with a query
    I have?

    I have a macro that currently parses a file looking for certain text
    and when it finds it copies the string to a given cell in an Excel
    file.

    I need to adapt this to now look for the line that contains a certain
    string of text and then import the following line of text to the cell
    in Excel (where I can then manipulate it using text to columns).

    Could someone please gie me a hint on how to accomplish this?

    My code that needs altering is:

    Open FName For Input As Fnum
    i = 1

    Do While Not EOF(Fnum)
    If InStr(1, sLine, "NUE00001 GRAND TOTALS", vbTextCompare) > 0 Then

    ' This is the bit I need to change to import the line following the
    matching row....

    Cells(3, 3).Value = sLine

    End If


    Thanks in advance,

    Richard


  2. #2
    Dave Peterson
    Guest

    Re: Parsing a Text File

    Are you picking up a single line from the file or maybe multiple lines?

    A single line:

    Option Explicit
    Sub testme()

    Dim FName As String
    Dim KeepTheNext As Boolean
    Dim FNum As Long
    Dim i As Long
    Dim sLine As String

    FName = "C:\my documents\excel\text1.txt"

    FNum = FreeFile

    Open FName For Input As FNum
    i = 3

    KeepTheNext = False
    Do While Not EOF(FNum)
    Input #FNum, sLine
    If InStr(1, sLine, "NUE00001 GRAND TOTALS", vbTextCompare) > 0 Then
    KeepTheNext = True
    Else
    If KeepTheNext = True Then
    KeepTheNext = False
    Cells(i, 3).Value = sLine
    Exit Do 'if you just want one line from the file
    'or go to the next row for the next match???
    'i = i + 1
    End If
    End If
    Loop

    End Sub

    If you wanted to pick up each of the lines after every match, get rid of the
    "exit do" and just drop down a row to get ready for the next line.

    (I use i as the row counter--I didn't see how it was used in your code.)



    [email protected] wrote:
    >
    > Good Evening,
    >
    > I wonder if somebody could point me in the right direction with a query
    > I have?
    >
    > I have a macro that currently parses a file looking for certain text
    > and when it finds it copies the string to a given cell in an Excel
    > file.
    >
    > I need to adapt this to now look for the line that contains a certain
    > string of text and then import the following line of text to the cell
    > in Excel (where I can then manipulate it using text to columns).
    >
    > Could someone please gie me a hint on how to accomplish this?
    >
    > My code that needs altering is:
    >
    > Open FName For Input As Fnum
    > i = 1
    >
    > Do While Not EOF(Fnum)
    > If InStr(1, sLine, "NUE00001 GRAND TOTALS", vbTextCompare) > 0 Then
    >
    > ' This is the bit I need to change to import the line following the
    > matching row....
    >
    > Cells(3, 3).Value = sLine
    >
    > End If
    >
    > Thanks in advance,
    >
    > Richard


    --

    Dave Peterson

  3. #3
    Tom Ogilvy
    Guest

    Re: Parsing a Text File


    If the objective is to just do what you describe, you could simplify it to:
    (assumes there will be a line after the line you are looking for)

    Sub testme()
    Dim FName As String
    Dim FNum As Long
    Dim sLine As String

    FName = "C:\my documents\excel\text1.txt"

    FNum = FreeFile

    Open FName For Input As FNum
    Do While Not EOF(FNum)
    Input #FNum, sLine
    If InStr(1, sLine, _
    "NUE00001 GRAND TOTALS", _
    vbTextCompare) > 0 Then
    Input #FNum, sLine
    Cells(3, 3).Value = sLine
    End If
    Loop
    ' now close the file
    close #Fnum
    End Sub

    --
    Regards,
    Tom Ogilvy




    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > Are you picking up a single line from the file or maybe multiple lines?
    >
    > A single line:
    >
    > Option Explicit
    > Sub testme()
    >
    > Dim FName As String
    > Dim KeepTheNext As Boolean
    > Dim FNum As Long
    > Dim i As Long
    > Dim sLine As String
    >
    > FName = "C:\my documents\excel\text1.txt"
    >
    > FNum = FreeFile
    >
    > Open FName For Input As FNum
    > i = 3
    >
    > KeepTheNext = False
    > Do While Not EOF(FNum)
    > Input #FNum, sLine
    > If InStr(1, sLine, "NUE00001 GRAND TOTALS", vbTextCompare) > 0 Then
    > KeepTheNext = True
    > Else
    > If KeepTheNext = True Then
    > KeepTheNext = False
    > Cells(i, 3).Value = sLine
    > Exit Do 'if you just want one line from the file
    > 'or go to the next row for the next match???
    > 'i = i + 1
    > End If
    > End If
    > Loop
    >
    > End Sub
    >
    > If you wanted to pick up each of the lines after every match, get rid of

    the
    > "exit do" and just drop down a row to get ready for the next line.
    >
    > (I use i as the row counter--I didn't see how it was used in your code.)
    >
    >
    >
    > [email protected] wrote:
    > >
    > > Good Evening,
    > >
    > > I wonder if somebody could point me in the right direction with a query
    > > I have?
    > >
    > > I have a macro that currently parses a file looking for certain text
    > > and when it finds it copies the string to a given cell in an Excel
    > > file.
    > >
    > > I need to adapt this to now look for the line that contains a certain
    > > string of text and then import the following line of text to the cell
    > > in Excel (where I can then manipulate it using text to columns).
    > >
    > > Could someone please gie me a hint on how to accomplish this?
    > >
    > > My code that needs altering is:
    > >
    > > Open FName For Input As Fnum
    > > i = 1
    > >
    > > Do While Not EOF(Fnum)
    > > If InStr(1, sLine, "NUE00001 GRAND TOTALS", vbTextCompare) > 0 Then
    > >
    > > ' This is the bit I need to change to import the line following the
    > > matching row....
    > >
    > > Cells(3, 3).Value = sLine
    > >
    > > End If
    > >
    > > Thanks in advance,
    > >
    > > Richard

    >
    > --
    >
    > Dave Peterson




  4. #4
    Dave Peterson
    Guest

    Re: Parsing a Text File

    I wasn't sure and I wanted to make it easy enough to modify in either case.

    Thanks for the correction to include the close statement.



    Tom Ogilvy wrote:
    >
    > If the objective is to just do what you describe, you could simplify it to:
    > (assumes there will be a line after the line you are looking for)
    >
    > Sub testme()
    > Dim FName As String
    > Dim FNum As Long
    > Dim sLine As String
    >
    > FName = "C:\my documents\excel\text1.txt"
    >
    > FNum = FreeFile
    >
    > Open FName For Input As FNum
    > Do While Not EOF(FNum)
    > Input #FNum, sLine
    > If InStr(1, sLine, _
    > "NUE00001 GRAND TOTALS", _
    > vbTextCompare) > 0 Then
    > Input #FNum, sLine
    > Cells(3, 3).Value = sLine
    > End If
    > Loop
    > ' now close the file
    > close #Fnum
    > End Sub
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    > "Dave Peterson" <[email protected]> wrote in message
    > news:[email protected]...
    > > Are you picking up a single line from the file or maybe multiple lines?
    > >
    > > A single line:
    > >
    > > Option Explicit
    > > Sub testme()
    > >
    > > Dim FName As String
    > > Dim KeepTheNext As Boolean
    > > Dim FNum As Long
    > > Dim i As Long
    > > Dim sLine As String
    > >
    > > FName = "C:\my documents\excel\text1.txt"
    > >
    > > FNum = FreeFile
    > >
    > > Open FName For Input As FNum
    > > i = 3
    > >
    > > KeepTheNext = False
    > > Do While Not EOF(FNum)
    > > Input #FNum, sLine
    > > If InStr(1, sLine, "NUE00001 GRAND TOTALS", vbTextCompare) > 0 Then
    > > KeepTheNext = True
    > > Else
    > > If KeepTheNext = True Then
    > > KeepTheNext = False
    > > Cells(i, 3).Value = sLine
    > > Exit Do 'if you just want one line from the file
    > > 'or go to the next row for the next match???
    > > 'i = i + 1
    > > End If
    > > End If
    > > Loop
    > >
    > > End Sub
    > >
    > > If you wanted to pick up each of the lines after every match, get rid of

    > the
    > > "exit do" and just drop down a row to get ready for the next line.
    > >
    > > (I use i as the row counter--I didn't see how it was used in your code.)
    > >
    > >
    > >
    > > [email protected] wrote:
    > > >
    > > > Good Evening,
    > > >
    > > > I wonder if somebody could point me in the right direction with a query
    > > > I have?
    > > >
    > > > I have a macro that currently parses a file looking for certain text
    > > > and when it finds it copies the string to a given cell in an Excel
    > > > file.
    > > >
    > > > I need to adapt this to now look for the line that contains a certain
    > > > string of text and then import the following line of text to the cell
    > > > in Excel (where I can then manipulate it using text to columns).
    > > >
    > > > Could someone please gie me a hint on how to accomplish this?
    > > >
    > > > My code that needs altering is:
    > > >
    > > > Open FName For Input As Fnum
    > > > i = 1
    > > >
    > > > Do While Not EOF(Fnum)
    > > > If InStr(1, sLine, "NUE00001 GRAND TOTALS", vbTextCompare) > 0 Then
    > > >
    > > > ' This is the bit I need to change to import the line following the
    > > > matching row....
    > > >
    > > > Cells(3, 3).Value = sLine
    > > >
    > > > End If
    > > >
    > > > Thanks in advance,
    > > >
    > > > Richard

    > >
    > > --
    > >
    > > Dave Peterson


    --

    Dave Peterson

  5. #5
    rstroughair
    Guest

    Re: Parsing a Text File

    Thanks for both of your answers, which worked great! However, could I
    trouble you for one more thing?

    I do indeed need to pick up multiple lines from the file. In most cases
    these are the following line after the match, but on a couple of
    occasions the line required in always two lines below the match. How do
    I alter the code to cater for this (eg. assume in the given example the
    match occurs on row 12 of the text file and I need to copy row 14 to
    the Excel cell)?

    Thanks again,

    Richard


  6. #6
    rstroughair
    Guest

    Re: Parsing a Text File

    Thanks for your replies, but I seem to have got it working now!

    This is the code I have used - is this the most efficient way?

    Sub testme2()
    Dim FName As String
    Dim FNum As Long
    Dim sLine As String
    Dim TName As Variant

    TName = Application.GetOpenFilename _
    (fileFilter:="Text Files (*.txt),*.txt,All Files (*.*),*.*", _
    Title:="Open Report")
    If TName = False Then
    MsgBox "You didn't select a file"
    'Exit Sub
    Cleanup
    End
    End If

    FName = CStr(TName)

    FNum = FreeFile

    Open FName For Input As FNum
    Do While Not EOF(FNum)
    Input #FNum, sLine
    If InStr(1, sLine, "NUE00001 GRAND TOTALS", vbTextCompare) > 0 Then
    Line Input #FNum, sLine 'Match where
    next line is needed
    Cells(3, 3).Value = sLine
    ElseIf InStr(1, sLine, "NUE00002 GRAND TOTALS", vbTextCompare) > 0
    Then
    Input #FNum, sLine
    Line Input #FNum, sLine 'Match where
    line 2 down is needed
    Cells(4, 3).Value = sLine
    End If
    Loop
    ' now close the file
    Close #FNum
    End Sub

    Richard


  7. #7
    Tom Ogilvy
    Guest

    Re: Parsing a Text File

    Sub testme2()
    Dim FName As String
    Dim FNum As Long
    Dim sLine As String
    Dim TName As Variant

    TName = Application.GetOpenFilename _
    (fileFilter:="Text Files (*.txt),*.txt,All Files (*.*),*.*", _
    Title:="Open Report")
    If TName = False Then
    MsgBox "You didn't select a file"
    'Exit Sub
    Cleanup
    End
    End If

    FName = CStr(TName)

    FNum = FreeFile

    Open FName For Input As FNum
    Do While Not EOF(FNum)
    Input #FNum, sLine
    If InStr(1, sLine, "NUE00001 GRAND TOTALS", _
    vbTextCompare) > 0 Then
    Line Input #FNum, sLine 'Match where next line is needed
    Cells(3, 3).Value = sLine
    ElseIf InStr(1, sLine, "NUE00002 GRAND TOTALS", _
    vbTextCompare) > 0 Then
    Line Input #FNum, sLine
    Line Input #FNum, sLine 'Match where line 2 down is needed
    Cells(4, 3).Value = sLine
    End If
    Loop
    ' now close the file
    Close #FNum
    End Sub

    It is unclear, but if you are done after you write the line, you can put in
    Exit Do within Your If construct after you write the line. Also, I correct
    one line that used Input instead of Line Input

    --
    Regards,
    Tom Ogilvy



    "rstroughair" <[email protected]> wrote in message
    news:[email protected]...
    > Thanks for your replies, but I seem to have got it working now!
    >
    > This is the code I have used - is this the most efficient way?
    >
    > Sub testme2()
    > Dim FName As String
    > Dim FNum As Long
    > Dim sLine As String
    > Dim TName As Variant
    >
    > TName = Application.GetOpenFilename _
    > (fileFilter:="Text Files (*.txt),*.txt,All Files (*.*),*.*", _
    > Title:="Open Report")
    > If TName = False Then
    > MsgBox "You didn't select a file"
    > 'Exit Sub
    > Cleanup
    > End
    > End If
    >
    > FName = CStr(TName)
    >
    > FNum = FreeFile
    >
    > Open FName For Input As FNum
    > Do While Not EOF(FNum)
    > Input #FNum, sLine
    > If InStr(1, sLine, "NUE00001 GRAND TOTALS", vbTextCompare) > 0 Then
    > Line Input #FNum, sLine 'Match where
    > next line is needed
    > Cells(3, 3).Value = sLine
    > ElseIf InStr(1, sLine, "NUE00002 GRAND TOTALS", vbTextCompare) > 0
    > Then
    > Input #FNum, sLine
    > Line Input #FNum, sLine 'Match where
    > line 2 down is needed
    > Cells(4, 3).Value = sLine
    > End If
    > Loop
    > ' now close the file
    > Close #FNum
    > End Sub
    >
    > Richard
    >




  8. #8
    Dave Peterson
    Guest

    Re: Parsing a Text File

    Oops and thanks for that last correction, too <bg>.

    Tom Ogilvy wrote:
    >
    > Sub testme2()
    > Dim FName As String
    > Dim FNum As Long
    > Dim sLine As String
    > Dim TName As Variant
    >
    > TName = Application.GetOpenFilename _
    > (fileFilter:="Text Files (*.txt),*.txt,All Files (*.*),*.*", _
    > Title:="Open Report")
    > If TName = False Then
    > MsgBox "You didn't select a file"
    > 'Exit Sub
    > Cleanup
    > End
    > End If
    >
    > FName = CStr(TName)
    >
    > FNum = FreeFile
    >
    > Open FName For Input As FNum
    > Do While Not EOF(FNum)
    > Input #FNum, sLine
    > If InStr(1, sLine, "NUE00001 GRAND TOTALS", _
    > vbTextCompare) > 0 Then
    > Line Input #FNum, sLine 'Match where next line is needed
    > Cells(3, 3).Value = sLine
    > ElseIf InStr(1, sLine, "NUE00002 GRAND TOTALS", _
    > vbTextCompare) > 0 Then
    > Line Input #FNum, sLine
    > Line Input #FNum, sLine 'Match where line 2 down is needed
    > Cells(4, 3).Value = sLine
    > End If
    > Loop
    > ' now close the file
    > Close #FNum
    > End Sub
    >
    > It is unclear, but if you are done after you write the line, you can put in
    > Exit Do within Your If construct after you write the line. Also, I correct
    > one line that used Input instead of Line Input
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    > "rstroughair" <[email protected]> wrote in message
    > news:[email protected]...
    > > Thanks for your replies, but I seem to have got it working now!
    > >
    > > This is the code I have used - is this the most efficient way?
    > >
    > > Sub testme2()
    > > Dim FName As String
    > > Dim FNum As Long
    > > Dim sLine As String
    > > Dim TName As Variant
    > >
    > > TName = Application.GetOpenFilename _
    > > (fileFilter:="Text Files (*.txt),*.txt,All Files (*.*),*.*", _
    > > Title:="Open Report")
    > > If TName = False Then
    > > MsgBox "You didn't select a file"
    > > 'Exit Sub
    > > Cleanup
    > > End
    > > End If
    > >
    > > FName = CStr(TName)
    > >
    > > FNum = FreeFile
    > >
    > > Open FName For Input As FNum
    > > Do While Not EOF(FNum)
    > > Input #FNum, sLine
    > > If InStr(1, sLine, "NUE00001 GRAND TOTALS", vbTextCompare) > 0 Then
    > > Line Input #FNum, sLine 'Match where
    > > next line is needed
    > > Cells(3, 3).Value = sLine
    > > ElseIf InStr(1, sLine, "NUE00002 GRAND TOTALS", vbTextCompare) > 0
    > > Then
    > > Input #FNum, sLine
    > > Line Input #FNum, sLine 'Match where
    > > line 2 down is needed
    > > Cells(4, 3).Value = sLine
    > > End If
    > > Loop
    > > ' now close the file
    > > Close #FNum
    > > End Sub
    > >
    > > Richard
    > >


    --

    Dave Peterson

  9. #9
    rstroughair
    Guest

    Re: Parsing a Text File

    Thanks again for your help Tom / Dave.

    Richard


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1