How to export comma delimited data to excel sheet, but each row is a new excel sheet

Question

i have a comma delimited text file as follows

RLGAcct#,PAYMENT_AMOUNT,TRANSACTION_DATE,CONSUMER_NAME,CONSUMER_ADD_STREET,CONSUMER_ADD_CSZ,CONSUMER_PHONE,CONSUMER_EMAIL,LAST_FOUR
ZTEST01,50.00,11/15/2018,ROBERT R SMITH,12345 SOME STREET,60046,,adam@adamparks.com,2224
ZTEST02,100.00,11/15/2018,ROBERT JONES,5215 OLD ORCHARD RD,60077,,adam@adamparks.com,2223
ZTEST03,75.00,11/15/2018,JAMES B MCDONALD,4522 N CENTRAL PARK AVE APT 2,60625,,adam@adamparks.com,2222
ZTEST04,80.00,11/15/2018,JOHN Q DOE,919 W 33RD PL 2ND FL,60608,,adam@adamparks.com,2221
ZTEST05,60.00,11/15/2018,SAMANTHAN STEVENSON,123 MAIN ST,60610,,adam@adamparks.com,2220

I need to export this to excel so that each value between a comma is inserted into a column in excel

So

ZTEST01 is in A1, 
50.00 is in B1
11/15/2018 in C1 ...

The thing is i need each row to be inserted into a newly created excel worksheet.

The code i have is as follows:

 Dim xlApp As New Excel.Application
    Dim xlWorkbook As Excel.Workbook
    Dim xlWorksheet As Excel.Worksheet

    Private Sub BackgroundWorker1_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork

        'xlWorkbook = xlApp.workboos.Add() using this later once i have the parsing figured out

Dim columns As New List(Of String)
        Dim ccPayment = "C:\Users\XBorja.RESURGENCE\Downloads\Payments_Credit.txt"

        Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(ccPayment)

            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. 
                    For Each r In currentRow


                        columns.Add(r)
                        C

                    Next r



                Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("Line " & ex.Message & " is invalid.  Skipping")
                End Try

            End While

            'Dim index0 = columns(0)
            'Dim index1 = columns(1)
            'Dim index2 = columns(3)
            'Dim index3 = columns(3)
            'Dim index4 = columns(4)
            'Dim index5 = columns(5)
            'Dim index6 = columns(6)
            'Dim index7 = columns(7)
            'Dim index8 = columns(8)

            'Console.WriteLine(index0 & index1 & index2 & index3 & index4 & index5 & index6 & index7 & index8)


        End Using

        For Each r In columns
            Console.WriteLine(r)
        Next

end sub

As you can see I was trying to see if i could index these so that i could possibly equate each one to a cell in excel.

The other problem is that this text file changes daily. The columns are always set (9 columns) but the rows change dynamically daily based on how many transactions we get.

Answer 1

I would recommend using the EPPlus package which is available via NuGet. It removes the COM challenges of working with Excel and works by reading and writing the XLSX spreadsheet files.

The following sample does what you where asking:

Private Sub btnStackOverflowQuestion_Click(sender As Object, e As EventArgs) Handles btnStackOverflowQuestion.Click
    Dim ccPayment As String = "C:\temp\so.csv"

    Using pkg As New ExcelPackage()
        Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(ccPayment)
            MyReader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
            MyReader.Delimiters = New String() {","}
            Dim sheetCount As Integer
            While Not MyReader.EndOfData
                sheetCount += 1
                Dim newSheet As ExcelWorksheet = pkg.Workbook.Worksheets.Add($"Sheet{sheetCount}")
                Try
                    Dim currentRow As String() = MyReader.ReadFields()
                    Dim columnCount As Integer = 0
                    For Each r In currentRow
                        columnCount += 1
                        newSheet.Cells(1, columnCount).Value = r
                    Next r
                Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("Line " & ex.Message & " is invalid.  Skipping")
                End Try
            End While
        End Using
        Dim fi As New FileInfo("C:\temp\so.xlsx")
        pkg.SaveAs(fi)
    End Using
End Sub