We help IT Professionals succeed at work.

Ignore empty lines from csv file

2,771 Views
Last Modified: 2014-05-23
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

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2007

Commented:
Try this slight adjustment:

For Each ss As String In sLines
  If Trim(ss) = "" Then 'or compare against string.empty
....
CERTIFIED EXPERT
Top Expert 2007

Commented:
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 = )

Author

Commented:
Thanks sirbounty.  I've tried all 3 suggestions, but the two empty rows are still coming in.

Author

Commented:
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...
CERTIFIED EXPERT
Top Expert 2007

Commented:
Could you post a snippet of the source file, obfuscating any sensitive data, or course...

Author

Commented:
Here is the file I'm working with.
AA-READY.csv
CERTIFIED EXPERT
Top Expert 2007

Commented:
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

Author

Commented:
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.
CERTIFIED EXPERT
Top Expert 2007

Commented:
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...

Author

Commented:
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

CERTIFIED EXPERT
Top Expert 2007
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Perfecto! Many thanks Sir.
CERTIFIED EXPERT
Top Expert 2007

Commented:
Happy to help - thanks for the grade! :^)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.