Parsing a text file and sending a value to an excel worksheet

Posted on 2007-07-30
Last Modified: 2010-04-23
I have a large text file that I will need to parse through on a daily basis searching for one value.  That value will then need to be sent to an excel spreadsheet and pasted into cell A1.  

In the text file I can search for the words "Grans UnTraced" and I know that I need to grab the next number after that.  For example, here's what that section of the text file looks like.

  0.00          653.60          100.14          141.48       10,188.64
           Grand UnTraced:   50,690,470.29   30,706,358.74   13,584,834.09

I dont know what the exact paths of the text/excel file will be but for now lets just assume the txt file is located at c:\textfile\filename.txt and the excel is located at c:\excelfile\filename.xls.

Question by:collages
    LVL 96

    Expert Comment

    by:Bob Learned
    If you have VB.NET 2005, then you can use the Microsoft.VisualBasic.FileIO.TextFieldParser.

    LVL 27

    Accepted Solution

    Here's an example of how you can do this.  You will need to add a reference to the Microsoft Excel
    Object Library. (Project menu | Add Reference | Com tab)

        Private Sub SearchFile()
            Dim inputFile As String = "c:\textfile\filename.txt"
            Dim outputFile As String = "c:\excelfile\filename.xls"

            Dim sr As New System.IO.StreamReader(inputFile)
            Dim inLine As String

            Dim Value As String = String.Empty

            Dim iLocation As Int16 = -1
            Dim iDecimal As Int16 = -1
            Do Until sr.EndOfStream = True
                inLine = sr.ReadLine()

                iLocation = inLine.IndexOf("Grand UnTraced:")
                If iLocation > -1 Then
                    'found it
                    iLocation += "Grand UnTraced:".Length + 1

                    iDecimal = inLine.IndexOf(".", iLocation)

                    Value = inLine.Substring(iLocation, ((iDecimal) - iLocation) + 3).Trim()

                    Exit Do
                End If




            Dim ExcelApp As New Excel.Application
            Dim ExcelBook As Excel.Workbook = ExcelApp.Workbooks.Add
            Dim ExcelWorkSheet As Excel.Worksheet = CType(ExcelBook.Worksheets(1), Excel.Worksheet)

            ExcelWorkSheet.Range("A1").Value = Value




        End Sub

    LVL 1

    Author Comment

    Thanks a lot...this is very helpful.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Article by: jpaulino
    XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String ( Literal, only instead of starting and ending with w…
    A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now