Delimited Text File to Excel using vb.net

[zelrick]

Hello VB.net Programmers,

Please help about delimited. I've been looking to the forum and other sites about this but can't find any.

Here is my problem.

1. I need to put the text file to excel but this text file don't have comma and tab. This are separated only with spaces.

2. And also, I won't set a default text file. I need to open a file with different text file name. Example is test.txt or activity.txt or any name.txt

My work is just reading delimited tab, and it not working with spaces.

Please help. The code I'm using is:

    Process.Start("Excel", Label2.Text)

This code just opening text file as excel.

[ddanbe]

Google!
Plenty to find: example.

[zelrick]

Sir ddanbe,

I can't find the solution sir. The only thing they do is delimited tab and comma.

The text file that is created don't have comma nor tab. It has space which is related in delimited of excel. But I don't see an answer in google.

Example: (This is a Text File)

12345 12345 12345

This numbers separated by spaces.

Process.Start("Excel", Label2.Text)

'This code can run delimited tab but not spaces.

Please help

PS: I encounter error with my code above:

"External table is not in the expected format."

But when I remove the space between this numbers and make it Tab it works fine.

[Reverend Jim]

The easiest way to separate out numbers delimited by one or more blanks is with a regular expression. Here is a sample

Imports System.Text.RegularExpressions

Public Class Form1

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        Dim rex As New Regex("[0-9]+")
        Dim line As String = "   123     45    67   89   "

        For Each m As Match In rex.Matches(line)
            Debug.WriteLine(m.Value)
        Next

    End Sub

End Class

The expression [0-9]+ matches the longest possible string of digits. This works whether the delimiters are blanks, commans, or whatever. The caveat is that this works only on integers. If you want to include numbers with a decimal point use the pattern [0-9\.]+. To allow for an optional leading sign use [-+]*[0-9\.]+.

[zelrick]

Sir ddanbe,

Sir still got the output,

Am I doing it right? I put your code in the button who will open the text file in excel format;

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    Label3.Text = ("The file can now view to MS Excel")

    Dim rex As New Regex("[0-9]+")
    Dim line As String = "   123     45    67   89   "
    For Each m As Match In rex.Matches(line)
        Debug.WriteLine(m.Value)

    Next

    Process.Start("Excel", Label2.Text)

End Sub

I'm sorry the error before was not related in this case. I was in the wrong form when I run.
So this code:

Process.Start("Excel", Label2.Text)

It works fine but the output is wrong. It open in excel file but the whole one line was in one cell of the excel. It's like this 12345 12345 abcde abcde 12345 inside of the A1 cell. Then A2 is same 12345 12345 abcde.

I want 12345 in one cell then another 12345 in new cell and abcde in new cell and so on..

[zelrick]

Sir Reverend Jim,

Sorry the previous message was for you. I'm very tired and didn't notice.

I'm very sorry.

Godbless.

[Hardz]

Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        workbook = APP.Workbooks.Open(lblPath.Text) 'path of excel file
        worksheet = workbook.Worksheets("sheet1")

        Dim i As Integer = 1
        ' Split string based on spaces.
        ' using textbox1 for your text input for example, just put this character to textbox1: 12345 12345 abcde abcde 12345
        Dim words As String() = TextBox1.Text.Split(New Char() {" "})
        ' Use For Each loop over words and display them.
        Dim word As String
        For Each word In words
            worksheet.Cells(1, i).Value = word
            i = i + 1
        Next
        MessageBox.Show("successful...")
        workbook.Save()
        workbook.Close()
        APP.Quit()
        Process.Start("Excel", lblPath.Text)
    End Sub

[zelrick]

Sir Hardz,

already have a text file(.txt) that will be open with MS excel.

example: 12345 12345 abcde abcde 12345

the example is already in text file.

Sorry for misunderstanding my english.

I've done some few works but the text file was open in msgbox.

Here is the code I'm working now:

'Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\Users\abcde\Desktop\anothertest.txt")
    'MyReader.TextFieldType = FileIO.FieldType.Delimited
    'MyReader.SetDelimiters(" ")

    'Dim row As String()
    'While Not MyReader.EndOfData
    'Try
    'row = MyReader.ReadFields()
    'Dim field As String
    'For Each field In row
    'MsgBox(field)
    'Next
    'Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
    'MsgBox("line " & ex.Message & "is not valid and will be skipped.")
    'End Try
    'End While
    'End Using

    'Process.Start("Excel", Label2.Text)

[Hardz]

A little tweak using your code, which resulted on the following:

Dim APP As New Excel.Application
    Dim worksheet As Excel.Worksheet
    Dim workbook As Excel.Workbook
    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        workbook = APP.Workbooks.Open(lblPath.Text)
        worksheet = workbook.Worksheets("sheet1")

        Dim i As Integer = 1
        Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\Users\abcde\Desktop\anothertest.txt")
            MyReader.TextFieldType = FileIO.FieldType.Delimited
            MyReader.SetDelimiters(" ")
            Dim row As String()
            While Not MyReader.EndOfData
                Try
                    row = MyReader.ReadFields()
                    Dim field As String
                    For Each field In row
                        'MsgBox(field)
                        worksheet.Cells(1, i).Value = field
                        i = i + 1
                    Next
                Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("line " & ex.Message & "is not valid and will be skipped.")
                End Try
            End While
        End Using

        MessageBox.Show("successful...")
        workbook.Save()
        workbook.Close()
        APP.Quit()
        Process.Start("Excel", lblPath.Text)
    End Sub

[zelrick]

Hello Sir Hardz,

got some error in this line.

worksheet = workbook.Worksheets("[Sheet1$]")

I change the sheet1 because it is error. the [Sheet1$] also error. Please help me.

Error Appear: COMException was unhandled

Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))

[Hardz]

try remove the $ sign, and create a new excel file, use "sheet1" as default sheet name.

[zelrick]

Sir Hardz,

Same error Sir;

Sir the example above is a text file, and will be open in a new excel.

the Label2.Text in the code is a path where the text file located.

[Hardz]

Hi,

The sample code above indicates a separate path for text file and excel file. Where in the text file was located based on your sample at C:\Users\abcde\Desktop\anothertest.txt, while I created a .xlsx file and put its value to lblPath or Label2. Maybe you can try this procedure to see if it is working. And I will try your requirements later on.

[Reverend Jim]

You may also want to have a look at this code snippet.

[zelrick]

Hello Sir Hardz and Sir Reverend Jim,

Sorry for the late reply, I've done some revisions base on the link given by Sir Reverend.

I'm afraid I don't know how to do that Sir Hardz, but I change the path (C:\Users......) to Label2.Text

I have a OpenFileDialog that will change the Label2.Text where the text file current location(path);

But still I have the error on the:

worksheet = workbook.ActiveSheet("Sheet1")

Here is my new code:

Dim APP As New Excel.Application
    Dim workbook As Excel.Workbook = APP.ActiveWorkbook
    Dim worksheet As Excel.Worksheet

    workbook = APP.Workbooks.Open(Label2.Text)
    worksheet = workbook.ActiveSheet("Sheet1")

    Dim i As Integer = 1
    Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(Label2.Text)
        MyReader.TextFieldType = FileIO.FieldType.Delimited
        MyReader.SetDelimiters(" ")
        Dim row As String()
        While Not MyReader.EndOfData
            Try
                row = MyReader.ReadFields()
                Dim field As String
                For Each field In row
                    'MsgBox(field)
                    worksheet.Cells(1, i).Value = field
                    i = i + 1
                Next

            Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                MsgBox("line " & ex.Message & "is not valid and will be skipped.")
            End Try
        End While
    End Using
    MessageBox.Show("successful...")
    workbook.Save()
    workbook.Close()
    APP.Quit()
    Process.Start("Excel", Label2.Text)

Sorry again for my explanation; didn't clear everything. My Apologies;

[Reverend Jim]

Try

worksheet = workbook.ActiveSheet

[zelrick]

oh it works now, thank you Sir Reverend Jim;

but the result of excel it didn't use delimited and the whole line of the text was in A1(1st cell in excel)

I think the code:

Process.Start("Excel", lblPath.Text)

was the only running and ignored the other code;

[Hardz]

Hi,

I don't know if this code would help, but process.start won't work against delimeted text. Unless, if you use this method: Workbooks.OpenText.

Dim ExcelApp As New Excel.Application
        ExcelApp.Visible = True
        ExcelApp.Workbooks.OpenText(Filename:=lblPath.Text,
            ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
            , Space:=True, Other:=False)
        ExcelApp = Nothing
        GC.Collect()

As Reverend Jim said, it is better to Dispose orphaned excel.exe to avoid memory leak.

[zelrick]

Sir Hardz,

that code solve the problem, Thank you Sir Hardz & Sir Reverend Jim

But I just got an error in:

worksheet.Cells(1, i).Value = field

The result in excel is right, but why am I receiving this error?

Exception from HRESULT: 0x800401A8

Sir Hardz what GC.Collect for? didn't input that code but excel file is exact. Just error appear about worksheet.Cells

[Hardz]

Hi,

Just apply the new code to your button click event and remove/comment the old one and see what happened. Because just as I told a while ago, that the old sample indicates a separate path for text file and excel file (but don't worry it is working).

[zelrick]

Sir Hardz,

Really? got lotsa code earlier and now just 6 lines? and make the program run smooth. Everything gone in one snap.

Thank you Sir Hardz, I just did comment them for some review materials that might help me in the future. Gonna mark this topic solved.

Thank you very much Sir Hardz, Sir Reverend Jim, Sir ddanbe for the answer given.