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