Tag Archives: Excel.XlTextParsingType.xlDelimited

Excel Text To Columns From VB.net


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2012/06/29/excel-text-to-columns-from-vb-net/

 

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 2010 you can find that feature in the Data Tab.

image

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

image

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

image

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, _
            TrailingMinusNumbers:=False)
        End With
    End Sub
End Class
When you run the code this is the output that you get

image

If you are looking for a VBA version then you might want to look at this MSDN Site.

HTH