none
Text File to Excel Split delimiters RRS feed

  • Question

  • Hi. Its me again, with the excel problem.

    I am looking for method to convert from text file to excel file (.xlsx). but in the same time it also can split the delimiters ("|"). 

    I try and error this code, 

     Public Sub separatetxt()
    
                Dim fileToRead As String = "\3\pendingpgi_.txt"
                Dim fileStream As StreamReader = File.OpenText(fileToRead)
                Dim readContents As String = fileStream.ReadToEnd()
                Dim repContents As String = Replace(readContents, "|", vbTab)
                fileStream.Close()
                Dim filename As String = "\3\pendingpgi.xls"
                Dim convert As Boolean = SaveTextToFile(repContents, filename)
    
    
            End Sub
    
            Public Function SaveTextToFile(ByVal strData As String, ByVal FullPath As String, Optional ByVal ErrInfo As String = "") As Boolean
    
                Dim bAns As Boolean = False
                Dim objReader As StreamWriter
                Try
                    objReader = New StreamWriter(FullPath)
                    objReader.Write(strData)
                    objReader.Close()
                    bAns = True
                Catch Ex As Exception
                    ErrInfo = Ex.Message
                End Try
                Return bAns
    
            End Function

    using this method, I try to get the xlsx format, but cannot open the file because of the different format. 

    and it can work with xls format. 

    Please help me anyone, to get the excel file from text file. I am so stuck with this program for a very long time huhuuh T_T

    Thursday, June 13, 2019 2:49 AM

Answers

  • Hi,

    If the txt file has multiple rows,try to fix code:

     Public Sub ExportExcel(ByVal fileName As String, ByVal str As String)
            Dim saveFileName As String = fileName
            If saveFileName.IndexOf(":") < 0 Then Return
            Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
    
            If xlApp Is Nothing Then
                MessageBox.Show("Unable to create Excel object, maybe your machine does not have Excel installed")
                Return
            End If
    
            Dim workbooks As Microsoft.Office.Interop.Excel.Workbooks = xlApp.Workbooks
            Dim workbook As Microsoft.Office.Interop.Excel.Workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet)
            Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = CType(workbook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
    
    
            'For i As Integer = 0 To t
            '    worksheet.Cells(1, i + 1) = str.Split(vbTab)(i)
            'Next
    
            For j As Integer = 1 To str.Split(vbCrLf).Count
                For i As Integer = 0 To str.Split(vbCrLf)(j - 1).Split(vbTab).Count - 1
    
                    worksheet.Cells(j, i + 1) = str.Split(vbCrLf)(j - 1).Split(vbTab)(i)
                Next
            Next
            worksheet.Cells.EntireColumn.AutoFit()
            If saveFileName <> "" Then
    
                Try
                    workbook.Saved = True
                    workbook.SaveCopyAs(saveFileName)
                Catch ex As Exception
                    MessageBox.Show("There was an error exporting the file and the file may be being opened!" & vbLf & ex.Message)
                End Try
            End If
    
            xlApp.Quit()
            GC.Collect()
            MessageBox.Show("Export success!", "prompt message", MessageBoxButtons.OK)
        End Sub

    Best Regards,

    Alex


    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 stizalke Thursday, June 20, 2019 2:21 AM
    Thursday, June 20, 2019 2:06 AM

All replies

  • Hi,

    try my code:

    Imports System.IO
    Imports Microsoft.Office.Interop.Excel
    
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            separatetxt()
    
        End Sub
        Public Sub separatetxt()
    
            Dim fileToRead As String = "D:\test.txt"
            Dim fileStream As StreamReader = File.OpenText(fileToRead)
            Dim readContents As String = fileStream.ReadToEnd()
    
            Dim repContents As String = Replace(readContents, "|", vbTab)
            fileStream.Close()
            Dim filename As String = "D:\test.xlsx"
    
            ExportExcel(filename, repContents, repContents.Split(vbTab).Count - 1)
    
        End Sub
        Public Sub ExportExcel(ByVal fileName As String, ByVal str As String, ByVal t As Integer)
            Dim saveFileName As String = fileName
            If saveFileName.IndexOf(":") < 0 Then Return
            Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
    
            If xlApp Is Nothing Then
                MessageBox.Show("Unable to create Excel object, maybe your machine does not have Excel installed")
                Return
            End If
    
            Dim workbooks As Microsoft.Office.Interop.Excel.Workbooks = xlApp.Workbooks
            Dim workbook As Microsoft.Office.Interop.Excel.Workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet)
            Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = CType(workbook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
    
    
            For i As Integer = 0 To t
                worksheet.Cells(1, i + 1) = str.Split(vbTab)(i)
            Next
    
            If saveFileName <> "" Then
    
                Try
                    workbook.Saved = True
                    workbook.SaveCopyAs(saveFileName)
                Catch ex As Exception
                    MessageBox.Show("There was an error exporting the file and the file may be being opened!" & vbLf & ex.Message)
                End Try
            End If
    
            xlApp.Quit()
            GC.Collect()
            MessageBox.Show("Export success!", "prompt message", MessageBoxButtons.OK)
        End Sub
    End Class
    

    Best Regards,

    Alex


    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.

    Thursday, June 13, 2019 6:21 AM
  • Hi Alex,

    It works to convert excel to .xlsx. 

    But, the excel file is convert to column and not by row. What I mean is, 

    the excel file from the code is like this,

    name | address | email | Class | Alicia | 1222 St. James | 12A |

    when it supposed to be like this, 

    name | address | email | class |

    alicia | 1222 St. James| 12A    |

    sorry for that. 

    Thursday, June 20, 2019 12:25 AM
  • Hi,

    If the txt file has multiple rows,try to fix code:

     Public Sub ExportExcel(ByVal fileName As String, ByVal str As String)
            Dim saveFileName As String = fileName
            If saveFileName.IndexOf(":") < 0 Then Return
            Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
    
            If xlApp Is Nothing Then
                MessageBox.Show("Unable to create Excel object, maybe your machine does not have Excel installed")
                Return
            End If
    
            Dim workbooks As Microsoft.Office.Interop.Excel.Workbooks = xlApp.Workbooks
            Dim workbook As Microsoft.Office.Interop.Excel.Workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet)
            Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = CType(workbook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
    
    
            'For i As Integer = 0 To t
            '    worksheet.Cells(1, i + 1) = str.Split(vbTab)(i)
            'Next
    
            For j As Integer = 1 To str.Split(vbCrLf).Count
                For i As Integer = 0 To str.Split(vbCrLf)(j - 1).Split(vbTab).Count - 1
    
                    worksheet.Cells(j, i + 1) = str.Split(vbCrLf)(j - 1).Split(vbTab)(i)
                Next
            Next
            worksheet.Cells.EntireColumn.AutoFit()
            If saveFileName <> "" Then
    
                Try
                    workbook.Saved = True
                    workbook.SaveCopyAs(saveFileName)
                Catch ex As Exception
                    MessageBox.Show("There was an error exporting the file and the file may be being opened!" & vbLf & ex.Message)
                End Try
            End If
    
            xlApp.Quit()
            GC.Collect()
            MessageBox.Show("Export success!", "prompt message", MessageBoxButtons.OK)
        End Sub

    Best Regards,

    Alex


    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 stizalke Thursday, June 20, 2019 2:21 AM
    Thursday, June 20, 2019 2:06 AM
  • Hi, 

    Thanks a lot. 

    it works.

    I wish good things happen to you today and after. 

    Really thanks for your help.

    I really appreciate it. 

    Thursday, June 20, 2019 2:21 AM