There are many occasions where we need to open a text file in Excel and then arrange them in separate columns. If we directly open the file in Excel then we notice that everything is filled in Col A. In such a scenario what  can we do?

Excel has an inbuilt feature which is called Text To Columns which parses a column of cells that contain text into several columns. In Excel, you can find that feature in the Data Tab.

So how do we call this functionality from VB.Net? Let’s take an example. Copy the below text and paste it in a Notepad and save it as C:\Sample.Txt

Name, Age, Sex
Frank, 21, M
Matha, 22, F
Jack, 23, M
William, 35, M

Now if we open the file directly in Excel this is what we get.

Now let’s try and automate the entire process in VB.Net so that the data is distributed in separate columns.

Create a form and put a button on the form. Set the reference to Microsoft Excel Library. If you do not know how to do that then see this.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) _
    Handles Button1.Click
        '~~> Open the File
        xlWorkBook = xlApp.Workbooks.Open("C:\Sample.txt")

        '~~> Set reference to the 1st Sheet
        xlWorkSheet = xlWorkBook.Sheets(1)

        '~~> Display Excel
        xlApp.Visible = True

        With xlWorkSheet
            .Columns(1).TextToColumns( _
            Destination:=.Cells(1, 1), _
            DataType:=Excel.XlTextParsingType.xlDelimited, _
            TextQualifier:=Excel.XlTextQualifier.xlTextQualifierDoubleQuote, _
            ConsecutiveDelimiter:=False, _
            TAB:=False, _
            Semicolon:=False, _
            Comma:=True, _
            Space:=False, _
            Other:=False, _
        End With
    End Sub
End Class

When you run the code this is the output that you get