Link to home
Start Free TrialLog in
Avatar of coperations07
coperations07Flag for United States of America

asked on

Ignore empty lines from csv file

Hi,
I'm using VS2010 with VB.Net.

I have a function that reads in a csv file to a datatable. My problem is that the last couple rows in the datatable are blank. I need to ignore these rows.  I've tried checking if the Line Legnth is > 0, but it still brings in. I'm assuming the Legnth check doesn't work b/c there are commas in the line. I've tried checking the value of the first column in the datatable after each line writes to it and then deleting it if it is empty or null but this didn't work either. I would prefer to not write it to the datatable if I can catch it before. Here's what I'm working with...

            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 ss.Length > 0 Then
                    row = dtRecs.NewRow()
                    sLastLine = ss.Replace(Convert.ToString(ControlChars.Cr), "")
                    row.ItemArray = sLastLine.Split(","c)
                    dtRecs.Rows.Add(row)
                End If
            Next

Open in new window

Avatar of sirbounty
sirbounty
Flag of United States of America image

Try this slight adjustment:

For Each ss As String In sLines
  If Trim(ss) = "" Then 'or compare against string.empty
....
I'm assuming the Legnth check doesn't work b/c there are commas in the line.

If that's the case, then your comparison could be modified to:

If Trim(ss.replace(",","")) <> ""

(and the above should also be <>, not = )
Avatar of coperations07

ASKER

Thanks sirbounty.  I've tried all 3 suggestions, but the two empty rows are still coming in.
I didn't realize this earlier, but this started out bringing in 3 empty rows.  With the empty line check in place it only brings in 2 empty rows.  I just saved the csv as a textfile and opened it up. The textfile looks like it has 1 empty row at the bottom. So now I'm thinking I may be looping too many times and adding extra rows...
Could you post a snippet of the source file, obfuscating any sensitive data, or course...
Here is the file I'm working with.
AA-READY.csv
I'm surprised the 2nd suggestion didn't work...here's the full block

 
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

Open in new window

Yes that is the same block that I am using. I copy/pasted yours in just to be sure, but it is the same.  I'm setting the dtRecs table as a datasource for a datagridview so I could see its contents.  One of the empty rows I was seeing was just because the allowusertoaddrow setting was on. I set that to false and it only has 1 empty row now.
Where are you seeing the empty row?  This is in a datagrid?
Wonder if you could post the source code, because this appears to work for me...
Here's most of what is running in this event. The btnRun click kicks it off.

    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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of sirbounty
sirbounty
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perfecto! Many thanks Sir.
Happy to help - thanks for the grade! :^)