• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • 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.

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

Bob
0
 
VBRocksCommented:
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

        Loop

        'MsgBox(Value)

        sr.Close()
        sr.Dispose()

        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
        ExcelWorkSheet.Columns.AutoFit()

        ExcelBook.SaveAs(outputFile)

        ExcelBook.Close()

        MsgBox("Done")

    End Sub

0
 
collagesAuthor Commented:
Thanks a lot...this is very helpful.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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