Adding a missing delimiter to a .CSV file Visual Basic vb.net

Question

So I have written (cobbled) together a program that takes a .CSV file and injects that into a database. The issue I'm running into is that the weekly report has a missing delimiter from the last row at the end. (This data can not be modified, except manually). I need to able to add the delimiter to the end of the last row to avoid the continual "MalformedLineException" I keep getting and the missing last row. Would love some help and advice.

Private Sub subProcessFile(ByVal strFileName As String)
    'This is the file location for the CSV File
    Using TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(strFileName)

        'removing the delimiter
        TextFileReader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
        TextFileReader.SetDelimiters(",")
        ProgressBar1.Value = 0
        Application.DoEvents()
        'variables
        Dim TextFileTable As DataTable = Nothing
        Dim Column As DataColumn
        Dim Row As DataRow
        Dim UpperBound As Int32
        Dim ColumnCount As Int32
        Dim CurrentRow As String()


        'Loop To read in data from CSV
        While Not TextFileReader.EndOfData
            Try

                CurrentRow = TextFileReader.ReadFields()

                If Not CurrentRow Is Nothing Then
                    ''# Check if DataTable has been created
                    If TextFileTable Is Nothing Then
                        TextFileTable = New DataTable("TextFileTable")
                        ''# Get number of columns
                        UpperBound = CurrentRow.GetUpperBound(0)
                        ''# Create new DataTable
                        For ColumnCount = 0 To UpperBound
                            Column = New DataColumn()
                            Column.DataType = System.Type.GetType("System.String")
                            Column.ColumnName = "Column" & ColumnCount
                            Column.Caption = "Column" & ColumnCount
                            Column.ReadOnly = True
                            Column.Unique = False
                            TextFileTable.Columns.Add(Column)

                            ProgressBar1.Value = 25
                            Application.DoEvents()
                        Next

                        clsDeletePipeLineData.main()

                    End If

                    Row = TextFileTable.NewRow
                    'Dim Rownum As Double = Row
                    'If Rownum >= 1715 Then
                    '    MsgBox(Row)
                    'End If
                    For ColumnCount = 0 To UpperBound
                        Row("Column" & ColumnCount) = CurrentRow(ColumnCount).ToString
                    Next
                    TextFileTable.Rows.Add(Row)
                    clsInsertPipeLineData.main(CurrentRow(0).ToString, CurrentRow(1).ToString, CurrentRow(2).ToString, CurrentRow(3).ToString, CurrentRow(4).ToString, CurrentRow(5).ToString, CurrentRow(6).ToString, CurrentRow(7).ToString, CurrentRow(9).ToString)
                    ProgressBar1.Value = 50
                    Application.DoEvents()
                End If

            Catch ex As _
            Microsoft.VisualBasic.FileIO.MalformedLineException
                MsgBox("Line " & ex.Message &
                "is not valid and will be skipped.")

            End Try
        End While
        ProgressBar1.Value = 100
        Application.DoEvents()
        clsMailConfirmation.main()
        TextFileReader.Dispose()
        MessageBox.Show("The process has been completed successfully")

    End Using

A good example of data would be:

"1","£10","Joe Bloggs"

a bad example of data would be:

"2","£50","Jane Smith

Thanks in advance!

Answer 1

You could use File.AppendAllText(strFileName, """") on the line before the Using ... if it consisently lacks the closing ", otherwise you might want to check the last character of the file first, to make it a little more robust.

While I'm looking at the code, there are perhaps some alterations that could be useful:

• I notice that the DataTable is not actually used anywhere, so you can speed up the Sub by removing that code.
• clsDeletePipeLineData.main() seems to only be called for the first time round in the loop, so a "flag variable" can be used to determine if it should be called.
• It is quite simple to add some code to check that the required number of columns are present before trying to access them with, e.g., currentRow(9).ToString(), and give some details in the error message.
• The purpose of the method is to put data in the database, so there shouldn't really be a MessageBox.Show in there. You could put it after the call to subProcessFile to give feedback to the user.

The first of those changes could make it fast enough that there is no need for a progress bar - perhaps setting the cursor to a wait cursor before calling the method and resetting it afterwards would give sufficient feedback. Using Application.DoEvents can make things go wrong in unexpected ways, so if it really does need a progress report then look into using a BackgroundWorker or an Async method as shown in C# in How to do progress reporting using Async/Await.

Option Infer On
Option Strict On

Imports Microsoft.VisualBasic.FileIO
Imports System.IO

' other code...

Private Sub subProcessFile(ByVal csvFilename As String)

    ' The CSV files are faulty because of a missing final double-quote. Add it.
    File.AppendAllText(csvFilename, """")

    Dim expectedCsvColumnCount = 10

    Using TextFileReader As New TextFieldParser(csvFilename) With {
        .TextFieldType = FieldType.Delimited,
        .Delimiters = {","},
        .HasFieldsEnclosedInQuotes = True}

        Dim lineNo = 0
        Dim firstRow = True

        While Not TextFileReader.EndOfData
            Try
                lineNo += 1
                Dim currentRow = TextFileReader.ReadFields()

                If currentRow IsNot Nothing Then
                    If firstRow Then
                        clsDeletePipeLineData.main()
                        firstRow = False
                    End If

                    If currentRow.Count = expectedCsvColumnCount Then
                        clsInsertPipeLineData.main(currentRow(0).ToString(), currentRow(1).ToString(), currentRow(2).ToString(), currentRow(3).ToString(), currentRow(4).ToString(), currentRow(5).ToString(), currentRow(6).ToString(), currentRow(7).ToString(), currentRow(9).ToString())
                    Else
                        Dim errMsg = String.Format("Wrong number of columns (expected {0}, found {1}) at line {2} in file ""{3}"".", expectedCsvColumnCount, currentRow.Count, lineNo, csvFilename)
                        Throw New InvalidDataException(errMsg)
                    End If

                End If

            Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                MsgBox("Line " & ex.Message & " is not valid and will be skipped.")
            End Try

        End While

        clsMailConfirmation.main()

    End Using

End Sub