StreamReader to excel?

[jigax]

Hello everyone.

I have a text file eg.

line1
line2
line3
line4
line5

I want to read these lines to excel cells a1,b1,c1 ect until it reaches the words "Date: " then start writing a2,b2,c2 ect. this is the code i have.

' Create an instance of StreamReader to read from a file.
Dim OpenFileDialog As OpenFileDialog = New OpenFileDialog
OpenFileDialog.Filter = "Text Files (*.txt)|*.txt|All Files (*.*)|*.*"
OpenFileDialog.Title = "Open File"
OpenFileDialog.ShowDialog()

Dim sr As StreamReader = New StreamReader(OpenFileDialog.FileName)
Dim line As String
' Read and display the lines from the file until the end
' of the file is reached.
Do
line = sr.ReadLine()
Console.WriteLine(line)
If line = "Date: " Then
Do
line = sr.ReadLine()
Loop Until line = "Service Order "
End If
Loop Until line Is Nothing
sr.Close()
Catch ex As Exception
MsgBox("Please select a file", MsgBoxStyle.OkOnly, "Error")
End Try

 

[jmcilhinney]

This question really has nothing to do with WinForms so it doesn't belong in the Windows Forms forum. Moved.

Does it need to be an XLS/XLSX file or would CSV be OK? If so then it's much easier because you can write a CSV using a StreamWriter. If not then it's more complex because you must automate the Excel application. Have you ever done that before?

 

[jigax]

CSV is fine and now I haven't done this. I have very little vb.net experience. Thanks in advance.

 

[jmcilhinney]

If you don't need to create an XLS/XLSX file then you don't need to use Excel automation so the last question is irrelevant.

You may need to make a few adjustments but this shows the general idea:

Using reader As New IO.StreamReader("input file path")
    Using writer As New IO.StreamWriter("output file path")
        Dim line As String

        Do Until reader.EndOfStream
            line = reader.ReadLine()

            'Write the field value.
            writer.Write(line)

            If line.StartsWith("Date") Then
                'Write a line terminator.
                writer.WriteLine()
            Else
                'Write a field terminator.
                writer.Write(",")
            End If
        Loop
    End Using
End Using

 

[jigax]

That works great! Thank you much!