DataTable Remove Rows with Blank field and create new table

[billboy]

I have a DataTable that I create by using the TextFieldParser against a txt file I want to keep that Table but I also want to create another table from the same file minus the rows where a certain field is empty.

I am not sure what the BEST way to accomplish this is. I thought of a function that would return a NewTable ?

Private Function EditTable(ByVal Table As DataTable) As DataTable

        For i As Integer = Table.Rows.Count - 1 To 0 Step -1
            Dim row As DataRow = Table.Rows(i)
            If row.Item(0) Is Nothing Then
                Table.Rows.Remove(row)
            ElseIf row.Item(0).ToString = "" Then
                Table.Rows.Remove(row)
            End If
        Next
        Return NewTable

    End Function

I am struggling to see what I am doing wrong, thought I would put up a post while I continue to google about functions and other possible solutions

[jmcilhinney]

You have two options:

1. Create one DataTable as you read the data and then create a second DataTable from the first.
2. Create both DataTables at the same time, as you read the data from the file.

To my mind, that second option seems the most logical, unless there's a good reason not to. If you want to go with the FIRST option then you don't remove any rows from any table. You would create a DataView from the first DataTable, set its RowFilter property to exclude specific rows and then call its ToTable method to create a new DataTable.

[billboy]

I agree and think option 2 is the way to go no reason not to that I can think of, why create something only to delete anyway if you can just not create it to begin with. So this is what I have but there seems to still be some fields that have no data in them that are being added, I dont know what else to test for ? I just have the msgobx temporarily to test

While Not myReader.EndOfData
                Try

                    currentRow = myReader.ReadFields()
                    Table.Rows.Add(currentRow)

                    If String.IsNullOrEmpty(myReader.ReadFields(0).ToString) = True Then
                        MsgBox("String was Null or Empty")
                    ElseIf myReader.ReadFields(0).ToString = "" Then
                        MsgBox("String was quotes")
                    Else
                        MsgBox(myReader.ReadFields(0).ToString)

                        Table2.Rows.Add(currentRow)

[jmcilhinney]

Whoa! You do know that ReadFields reads a line from the file, right? How many lines are you supposed to be reading in that snippet of code? Presumably one, so why are you calling ReadFields in four different places? You call ReadFields once. You've already done that and assigned the result to 'currentRow', so get rid of all those other ReadFields calls and use 'currentRow' each time you want to refer to the fields of the current line.

By the way, what does ReadFields return? A String array, right? What type is each element of a String array? It's a String, right? In that case, what exactly are those ToString calls supposed to achieve? Also, you call IsNullOrEmpty and then you compare to an empty String. If the field is an empty String then it will be detected by IsNullOrEmpty, so that comparison to an empty String can never produce a positive result.

[billboy]

Ok so now I am really confused. I understand the ToString calls not being necessary but lost everywhere else.

The Readfields returns an array of the 4 fields in the file

FName LName ID Email

I thought "If String.IsNullOrEmpty(myReader.ReadFields(0)) = True Then"

was testing to see if the first field was NullorEmpty ? if so it was skipping it

I was also test for "" and vbTab and Nothing

as no one test caught all instances of empty fields

I am trying to make 2 tables one with the empty fields and one without

[jmcilhinney]

I just don't understand how you can find this confusing:

You call ReadFields once. You've already done that and assigned the result to 'currentRow', so get rid of all those other ReadFields calls and use 'currentRow' each time you want to refer to the fields of the current line.

What does ReadFields do? It reads a line of the file into a String array, right? Obviously calling ReadFields multiple times reads multiple lines, otherwise what would be the point of calling it in a loop? So, let's look at your code:

While Not myReader.EndOfData
                Try

                    currentRow = myReader.ReadFields()
                    Table.Rows.Add(currentRow)

                    If String.IsNullOrEmpty(myReader.ReadFields(0).ToString) = True Then
                        MsgBox("String was Null or Empty")
                    ElseIf myReader.ReadFields(0).ToString = "" Then
                        MsgBox("String was quotes")
                    Else
                        MsgBox(myReader.ReadFields(0).ToString)

                        Table2.Rows.Add(currentRow)

On the first iteration of that loop, the blue line calls ReadFields so it reads the first line of the file into a String array and assign that to the 'currentRow' variable. The contents of that array is then added as a row to 'Table'. The red line then calls ReadFields again so it will read the second line of the file and test the first element of the resulting array. If that element is not null or empty then the orange line is executed, which calls ReadFields again and reads the third line of the file. It tests the first element of the resulting array to see if its empty. If it's not then you go on to the Else block, where the pink line calls ReadFields yet again, reading the fourth line of of the file. That means that every iteration of your loop can potentially read four lines of the file and will always read at least two. You are adding the contents of 'currentRow' to 'Table2' at the end of the Else block and yet not once do you actually test the contents of 'currentRow' to see whether it should be added to 'Table2' or not.

[billboy]

Oh my lord how obvious... Yes I understand I was calling the readfields over and over

think I have it now, it seems to be working

While Not myReader.EndOfData
                Try

                    currentRow = myReader.ReadFields()
                    Table.Rows.Add(currentRow)

                    If String.IsNullOrEmpty(currentRow(0)) = True Then

                    Else
                        Table2.Rows.Add(currentRow)
                    End If

                Catch ex As Exception
                End Try
            End While

[jmcilhinney]

Yes, that will work, but I still have a couple of issues with that code. Firstly, given that you can use Not here:

While Not myReader.EndOfData

why use this rather silly code:

If String.IsNullOrEmpty(currentRow(0)) = True Then

Else
    Table2.Rows.Add(currentRow)
End If

when you could do this:

If Not String.IsNullOrEmpty(currentRow(0)) Then
    Table2.Rows.Add(currentRow)
End If

Also, that's a dodgy exception handler. Exactly what exception(s) are you expecting to occur there? The only one that you should be considering is a MalformedLineException, although I think even then it would only be for certain formats, not including CSV. If you are looking to catch MalformedLineExceptions then you should be specifying that. You should pretty much never use Exception as the type in a Catch statement. If you do then you may end up catching OutOfMemoryExceptions and the like, which you should absolutely not be simply ignoring like that. The fact that you're ignoring exceptions is another issue. At the very least you should be outputting the details to a Debug listener so that you see them during testing. Simply put, good exception handling is not about putting Try...Catch blocks everywhere just in case. You should only ever use a Try...Catch block in places where you can expect an exception to reasonably be thrown and then only catch the specific exception(s) that can be thrown due to your code. Anything else should be handled by a generic exception handler at the application level, which means handling the UnhandledException event in a VB Windows app.

[billboy]

Thanks for the help and code suggestion , definately better then what I had

As far as the exception handling. I have much to learn about exception handling and will no doubt be reading and reading your advice