CODEWITHSUNDEEP
vb.net
slyclam
slyclam

Reputation: 290

VB Reading from text file, searching text and feeding to excel

I have a form with a RichTextBox where the the path of the input file is displayed. I also have a button in the form. The input text file is a large file with repetitions of the following chunk of data: 

        PROGRAM TRACING   (1)

    ON IA
    MPAG    (B) AR0=H'0000 7402         
    MTV     (B) VAR H'01 62AE:H'148=H'C350
    MTV     (B) VAR H'01 62AE:H'147=H'071F
    MTV     (B) VAR H'01 62AE:H'02B=H'4404
    MTV     (B) VAR H'01 62AE:H'02C=H'5712
    MTV     (B) VAR H'01 62AE:H'029=H'5405
    MTV     (B) VAR H'01 62AE:H'02A=H'F272
    MTV     (B) VAR H'01 62AE:H'093=H'3853
    MTV     (B) VAR H'01 62AE:H'094=H'2060
    MTV     (B) VAR H'01 62AE:H'091=H'3046
    MTV     (B) VAR H'01 62AE:H'092=H'F024
    MTV     (B) VAR H'01 62AE:H'1BB=H'0D07
    MTV     (B) VAR H'01 62AE:H'1BC=H'0B0B
    MTV     (B) VAR H'01 62AE:H'1BD=H'0616
    MTB     (B) VAR H'00 152E:H'00B(H'0001)=H'0079
    MTB     (B) VAR H'00 152E:H'00B(H'0002)=H'0019
    MTB     (B) VAR H'00 152E:H'00B(H'0003)=H'0062
    MTB     (B) VAR H'00 152E:H'00B(H'0004)=H'0086
    MTB     (B) VAR H'00 152E:H'00B(H'0005)=H'0041
    MTB     (B) VAR H'00 152E:H'00B(H'0006)=H'0000
    MTB     (B) VAR H'00 152E:H'00B(H'0007)=H'0000
    MBPAG   (B) VAR H'027=H'5970 C25F

END

What I need is : when the button is clicked, search the file for each line starting with MTV & MTB, the values appearing after "=" sign. For example starting with H'C350 ....to H'0000. Then call excel & feed the values in a single row for each chunk.

Please help regarding this.

Upvotes: 1

Views: 203

Answers (2)

sloth
sloth

Reputation: 101042

Full LinqPad-ready example:

Sub Main
    ' example data
    Dim data = <xml>
       PROGRAM TRACING   (1)

    ON IA
    MPAG    (B) AR0=H'0000 7402         
    MTV     (B) VAR H'01 62AE:H'148=H'C350
    MTV     (B) VAR H'01 62AE:H'147=H'071F
    MTV     (B) VAR H'01 62AE:H'02B=H'4404
    MTV     (B) VAR H'01 62AE:H'02C=H'5712
    MTV     (B) VAR H'01 62AE:H'029=H'5405
    MTV     (B) VAR H'01 62AE:H'02A=H'F272
    MTV     (B) VAR H'01 62AE:H'093=H'3853
    MTV     (B) VAR H'01 62AE:H'094=H'2060
    MTV     (B) VAR H'01 62AE:H'091=H'3046
    MTV     (B) VAR H'01 62AE:H'092=H'F024
    MTV     (B) VAR H'01 62AE:H'1BB=H'0D07
    MTV     (B) VAR H'01 62AE:H'1BC=H'0B0B
    MTV     (B) VAR H'01 62AE:H'1BD=H'0616
    MTB     (B) VAR H'00 152E:H'00B(H'0001)=H'0079
    MTB     (B) VAR H'00 152E:H'00B(H'0002)=H'0019
    MTB     (B) VAR H'00 152E:H'00B(H'0003)=H'0062
    MTB     (B) VAR H'00 152E:H'00B(H'0004)=H'0086
    MTB     (B) VAR H'00 152E:H'00B(H'0005)=H'0041
    MTB     (B) VAR H'00 152E:H'00B(H'0006)=H'0000
    MTB     (B) VAR H'00 152E:H'00B(H'0007)=H'0000
    MBPAG   (B) VAR H'027=H'5970 C25F

END
    </xml>

    ' create an excel instance
    Dim xl = Microsoft.VisualBasic.CreateObject("Excel.Application")
    xl.Visible = True
    Dim wb = xl.Workbooks.Add()
    Dim sheet = wb.ActiveSheet

    ' find lines starting with any whitepace followed by MTV or MTB and capture
    ' the text after =
    Dim pattern = "(?<=\s*(MTV|MTB).*=).*"

    Dim i = 1
    Dim arg = { Microsoft.VisualBasic.ControlChars.CrLf,  Microsoft.VisualBasic.ControlChars.Lf }

    ' You want to use File.ReadLines('yourfile.txt') here instead of data.Value.Split...
    For Each line in data.Value.Split(arg, StringSplitOptions.None)
        Dim match = Regex.Match(line, pattern)
        ' check each line and fill sheet
        If match.Success Then
            sheet.Cells(i, 1).Value = match.Value
            i += 1
        End If
    Next 
End Sub

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460048

Since i don't know how you are creating the excel-file (EPPlus, Interop, etc), i show you one way to get all substrings after the =:

Dim allData = From line In File.ReadLines("Your Path")
              Where line.TrimStart.StartsWith("MTV") OrElse line.TrimStart.StartsWith("MTB")
              Let index = line.LastIndexOf("=")
              Where index >= 0
              Select line.Substring(index + 1)

Upvotes: 0

Related Questions