• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

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

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.

1 Solution
Bob LearnedCommented:
If you have VB.NET 2005, then you can use the Microsoft.VisualBasic.FileIO.TextFieldParser.

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

collagesAuthor Commented:
Thanks a lot...this is very helpful.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now