Skip the first line of the CSV file (Headers) Visual Basic

Question

Like many on here, I am new to programming and mainly focus on web development. I have written a program cobbled together from help on here that works perfectly. I take a CSV file and inject it into an SQL database. I am getting a "MalformedLineException" line exception on the last line of the CSV file and believe it is because the header line is not being skipped.

Would love some help on working out how to skip the first line from my code below:

        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

If that's the problem then add a _TextFileReader.ReadFields()_ before entering the loop _ — Steve, Feb 11 '20 at 08:37
Use Debug to break and test the last line to make sure it really isn't the last line causing the issue. If it's the first line, either do as @Steve says, put a count in the loop and don't store if count=0 or remove row(0) from your table at the end. However as your error is "MalformedLineException" line exception on the last line of the CSV" don't just assume it's the header row causing the issue. — Jon Roberts, Feb 11 '20 at 09:09
If you want to skip a line then call `ReadLine` and discard the result. That said, why would an issue with the first line cause an exception on the last line? I suggest that you actually look at the data as it is read and see what that last line contains. — jmcilhinney, Feb 11 '20 at 09:22
There are multiple data files I use as they are pulled from weekly reports. When I remove the header row it puts the data into the database perfectly with no lost data. I would like to say a big thank you to you all, I really appreciate the help. — Michael O'Callaghan, Feb 11 '20 at 09:27
Jon - you were 100% right, the final record from every report I download is missing the delimiter. — Michael O'Callaghan, Feb 11 '20 at 10:58

score 1 · Accepted Answer · answered Feb 12 '20 at 12:52

"MalformedLineException" says that Line cannot be parsed using the current Delimiters, to fix it, adjust Delimiters so the line can be parsed correctly, or insert exception-handling code in order to handle the line. Someone encountered similar question, maybe its reply can help you.

Skip the first line of the CSV file (Headers) Visual Basic

1 Answers1