Ignore empty lines from csv file

I'm assuming the Legnth check doesn't work b/c there are commas in the line.

 
Dim row As DataRow
Dim sLastLine As String = ""
For Each ss As String In sLines
  If Trim(ss.replace(",","")) <> "" Then 
    row = dtRecs.NewRow()
    sLastLine = ss.Replace(Convert.ToString(ControlChars.Cr), "")
    row.ItemArray = sLastLine.Split(","c)
    dtRecs.Rows.Add(row)
  End If
Next

Select allOpen in new window

    Private Sub btnRun_Click(sender As System.Object, e As System.EventArgs) Handles btnRun.Click
        'Make sure occassion/program was selected. Also the room and mcc.
        sProgram = cboOcc.Text
        sRmMcc = txtRmMcc.Text
        If sProgram = "" Then
            MessageBox.Show("Select the Occassion first.")
            Exit Sub
        End If
        If sRmMcc = "" Then
            MessageBox.Show("Enter the room and mcc. Then try again.")
            Exit Sub
        End If
        'Open directory for file selection and import.
        Dim sImportFile As String
        Dim openFileDialog1 As New OpenFileDialog()
        openFileDialog1.Title = "Select the initial line setup file."
        openFileDialog1.InitialDirectory = "C:\Users\" & Environment.UserName & "\HOF\"
        openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"
        openFileDialog1.FilterIndex = 2
        openFileDialog1.RestoreDirectory = True

        sImportFile = openFileDialog1.ShowDialog()

        If sImportFile = System.Windows.Forms.DialogResult.OK Then
            Try
                'Call function to read the csv file and save it into the datatable.
                Dim dt As DataTable = ReadCSV(openFileDialog1.FileName)
                Dim sYear As String = dt.Rows(0)(1)
                Dim iCheck As Integer = ImportPrep1(sProgram, sYear) 'Check if data already exists. Clean out temp tables.
                dgvLSU.DataSource = dt 'testetestetsetest*****!!!
                Exit Sub
                '*************************************************
                If iCheck > 0 Then
                    Dim Answer As DialogResult = MessageBox.Show("The program and year for this data already exists in the database. Would you like to continue and purge the existing data for this program/year out and import the new data in?", _
                                                           "Program/Year already exists!", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation)
                    If Answer = Windows.Forms.DialogResult.Yes Then
                        'Remove the data from tbl_lsu and continue.
                        Dim sqlDel As String = "DELETE tbl_lsu WHERE program = '" & sProgram & "' AND progYear = '" & sYear & "'"
                        Dim con As New SqlConnection(My.Settings.setDB)
                        con.Open()
                        Dim cmd As New SqlCommand(sqlDel, con)
                        cmd.ExecuteNonQuery()
                        con.Close()
                    Else
                        Exit Sub
                    End If
                End If
                SaveToDatabase(dt) 'Call sub to insert the data from datatable to database table.
                Import1() 'update some stuff
                CreateFillBackup2() 'build fill and backup locations.
                AssignLocs3() 'assign newly built locations.
                Workbench5() 'insert data into workbench table.
                NeutralID()
                LSUFinal()
                dgvLSU.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)

            Catch Ex As Exception
                MessageBox.Show("Cannot read file from disk. Original error: " & Ex.Message)
            Finally

            End Try
        End If

    End Sub

    Function ReadCSV(ByVal path As String) As System.Data.DataTable
        'This function brings in the csv file. The first line is skipped because it contains headers.
        Try
            Dim sr As New StreamReader(path)
            sr.ReadLine() 'skip the header line
            Dim sFileData As String = sr.ReadToEnd()
            sr.Close()
            sr.Dispose()

            Dim sLines As String() = sFileData.Split(ControlChars.Lf)
            Dim dtRecs As New DataTable()
            Dim sArray As String() = sLines(0).Split(","c)

            For Each s As String In sArray
                dtRecs.Columns.Add(New DataColumn())
            Next

            'Dim row As DataRow
            'Dim sLastLine As String = ""
            'For Each ss As String In sLines

            '    If Trim(ss) <> String.Empty Then 'ignore any empty lines.
            '        row = dtRecs.NewRow()
            '        sLastLine = ss.Replace(Convert.ToString(ControlChars.Cr), "")
            '        row.ItemArray = sLastLine.Split(","c)
            '        dtRecs.Rows.Add(row)
            '    Else
            '        MessageBox.Show("tewst")
            '    End If
            'Next

            Dim row As DataRow
            Dim sLastLine As String = ""
            For Each ss As String In sLines
                If Trim(ss.Replace(",", "")) <> "" Then
                    row = dtRecs.NewRow()
                    sLastLine = ss.Replace(Convert.ToString(ControlChars.Cr), "")
                    row.ItemArray = sLastLine.Split(","c)
                    dtRecs.Rows.Add(row)
                End If
            Next


            ''Remove any blank rows. Flag all to delete and then call acceptchanges to delete the flagged rows. This retains the index during loop.
            'For i As Integer = 0 To dtRecs.Rows.Count - 1
            '    If dtRecs.Rows(i)(0) = "" Then
            '        dtRecs.Rows(i).Delete() 'flag to delete.
            '    End If
            'Next

            'dtRecs.AcceptChanges() 'delete flagged rows.
            Return dtRecs

        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Sub SaveToDatabase(ByVal dt As DataTable)
        'Insert the data from the datatable into the database table.
        Try
            Using cn As New SqlConnection(My.Settings.setDB)
                cn.Open()

                Using copy As New SqlBulkCopy(cn)
                    copy.DestinationTableName = "tbl_import"
                    copy.WriteToServer(dt)
                End Using
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            Dim sErr As String = ImportError(dt)
            MessageBox.Show(sErr)
        End Try

    End Sub

Select allOpen in new window