none
how to obtain all word from txt file and add it to excel sheet RRS feed

  • Question

  • Hi

    I want to develop software to get all strings from text file and put it in .xls excel sheet

    the txt file content will be like that

    1,1,0,1,1,NULL,ABC

    each word separated by ( , )

    So I want to application to get each word and number between ( , ) and add each one in a single cell in excel sheet

    I supposed that the application will ask me for the txt file location and in the end the application result will be .xls file

    Br,


    • Edited by Sam.sam.net Tuesday, November 28, 2017 2:21 PM
    Tuesday, November 28, 2017 2:20 PM

Answers

  • Hi Sam.sam.net,

    You want to export txt file into Excel file, I do one example that you can refer to :

    Dim XlApp As Excel.Application = New Excel.Application
            Dim XlWbk As Excel.Workbook = XlApp.Workbooks.Add
            XlApp.Visible = False
            Dim XlWkst As Excel.Worksheet = XlWbk.Worksheets.Item(1)
            Dim XlRng As Excel.Range = Nothing
    
            Dim sFileName As String
            Dim lines() As String
            sFileName = "D:\test\Test1.txt"
            lines = System.IO.File.ReadAllLines(sFileName)
    
            For I As Integer = 0 To lines.Length - 1
                XlRng = XlWkst.Range("a1").Offset(I, 0)
                Dim RC() As String = lines(I).Split(CChar(","))
                For J As Integer = 0 To RC.Length - 1
                    XlRng = XlRng.Offset(0, J)
                    XlRng.Value = RC(J)
                    XlRng = XlWkst.Range("a1").Offset(I, 0)
                Next
            Next
            XlApp.ActiveWorkbook.SaveAs("D:\test\Test5.xlsx")
            XlApp.Workbooks.Close()
            MessageBox.Show("OK!")

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sam.sam.net Wednesday, November 29, 2017 6:02 AM
    Wednesday, November 29, 2017 3:28 AM
    Moderator

All replies

  • I would suggest using a TextFieldParser to get at values. The example is a simple one taken from here where I altered the Delimiters property.

    Using MyReader As New Microsoft.VisualBasic.FileIO.
        TextFieldParser("c:\logs\bigfile")
    
        MyReader.TextFieldType = 
            Microsoft.VisualBasic.FileIO.FieldType.Delimited
        MyReader.Delimiters = New String() {","}
        Dim currentRow As String()
        'Loop through all of the fields in the file. 
        'If any lines are corrupt, report an error and continue parsing. 
        While Not MyReader.EndOfData
            Try
                currentRow = MyReader.ReadFields()
                ' Include code here to handle the row.
            Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                MsgBox("Line " & ex.Message & 
                " is invalid.  Skipping")
            End Try
        End While
    End Using

    Since you specified .xls you will need to look at using Excel automaton while if you were using .xlsx then I could had pointed you to a cool/free library to work with values from the above and placing them into cells in a worksheet.

    See the following for working with excel automation

    Basics of using Excel automation


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, November 28, 2017 3:15 PM
    Moderator
  • Hi Sam.sam.net,

    You want to export txt file into Excel file, I do one example that you can refer to :

    Dim XlApp As Excel.Application = New Excel.Application
            Dim XlWbk As Excel.Workbook = XlApp.Workbooks.Add
            XlApp.Visible = False
            Dim XlWkst As Excel.Worksheet = XlWbk.Worksheets.Item(1)
            Dim XlRng As Excel.Range = Nothing
    
            Dim sFileName As String
            Dim lines() As String
            sFileName = "D:\test\Test1.txt"
            lines = System.IO.File.ReadAllLines(sFileName)
    
            For I As Integer = 0 To lines.Length - 1
                XlRng = XlWkst.Range("a1").Offset(I, 0)
                Dim RC() As String = lines(I).Split(CChar(","))
                For J As Integer = 0 To RC.Length - 1
                    XlRng = XlRng.Offset(0, J)
                    XlRng.Value = RC(J)
                    XlRng = XlWkst.Range("a1").Offset(I, 0)
                Next
            Next
            XlApp.ActiveWorkbook.SaveAs("D:\test\Test5.xlsx")
            XlApp.Workbooks.Close()
            MessageBox.Show("OK!")

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sam.sam.net Wednesday, November 29, 2017 6:02 AM
    Wednesday, November 29, 2017 3:28 AM
    Moderator
  • Thank you very very very Much .. That's What i did

    Imports Microsoft.Office.Interop   ''''''''excel converter
    
    Public Class Form1
    
        ''''''''excel converter start
        Dim XlApp As Excel.Application = New Excel.Application
        Dim XlWbk As Excel.Workbook = XlApp.Workbooks.Add
        ' XlApp.Visible = False
        Dim XlWkst As Excel.Worksheet = XlWbk.Worksheets.Item(1)
        Dim XlRng As Excel.Range = Nothing
    
        Dim sFileName As String
        Dim lines() As String
    
        ''''''''excel converter end
    
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    
    
    
            XlRng = XlWkst.Range("b1").Offset(0, 0)
            XlRng.Value = ("tst123")
    
    
            sFileName = "D:\t.txt"
            lines = System.IO.File.ReadAllLines(sFileName)
    
    
            For I As Integer = 0 To lines.Length - 1
                XlRng = XlWkst.Range("b2").Offset(I, 0)
                Dim RC() As String = lines(I).Split(CChar(","))
                For J As Integer = 0 To RC.Length - 1
                    XlRng = XlRng.Offset(0, J)
                    XlRng.Value = RC(J)
    
                    XlRng = XlWkst.Range("b2").Offset(I, 0)
                Next
            Next
    
    
            XlApp.ActiveWorkbook.SaveAs("D:\t5.xlsx")
            XlApp.Workbooks.Close()
            'MessageBox.Show("OK!")
            'Me.Label7.Visible = True
    
        End Sub
    
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            XlRng = XlWkst.Range("b1").Offset(7, 2)
            XlRng.Value = ("123")
    
            XlApp.ActiveWorkbook.SaveAs("D:\t5.xlsx")
            XlApp.Workbooks.Close()
    
        End Sub
    
    
    End Class
    


    Friday, December 1, 2017 3:06 PM