Adding Columns Getting Type

I am reading a 3rd party text file using Text Field parser and then adding columns to a Table.

The program was working fine. The 3rd party must have changed the data type in one or more of the fields. So I now receive an input format error.

Unfortunately the way I currently have my code I can not determine which field is not correct. I am sure there is a better way to code this to provide message that lets me know which field is not being parsed? Can anyone give me a little help? Thank you

Here is the TextFieldParser code it creates all the columns as strings

Code:

---

Using myReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(filepath)
            myReader.SetDelimiters(vbTab)
            Dim currentRow As String()
            currentRow = myReader.ReadFields()
            Dim colNameList As New List(Of String)
            Dim colName As String = String.Empty
            For i As Integer = 0 To currentRow.GetUpperBound(0)
                colName = currentRow(i)
                Dim suffix As Integer = 1
                While colNameList.Contains(colName)
                    colName = currentRow(i) & suffix.ToString
                    suffix += 1
                End While
                colNameList.Add(colName)
            Next
            For Each currentField As String In colNameList
                table.Columns.Add(currentField, GetType(System.String))
            Next
            While Not myReader.EndOfData
                Try
                    currentRow = myReader.ReadFields()
                    table.Rows.Add(currentRow)
                Catch ex As Exception
                End Try
            End While
        End Using

---

Then here is how I am converting columns to doubles and time

Code:

---

With dtsold
            .Columns.Add("NewListPrice", GetType(Double))
            .Columns.Add("NewListDate", System.Type.GetType("System.DateTime"))
            .Columns.Add("NewDateSold", System.Type.GetType("System.DateTime"))
            .Columns.Add("NewAddress")
            .Columns.Add("DaysMarket", System.Type.GetType("System.Int16"))
            .Columns.Add("SoldPrice", GetType(Double))
            Dim dtrow As DataRow

            For Each dtrow In dtsold.Rows()
                Dim StrNum As String = dtrow.Item(Form10.add_Num_txt.Text).ToString
                Dim StrName As String = StrConv(dtrow.Item(Form10.add_Str_txt.Text).ToString, VbStrConv.ProperCase)
                Dim UnitNum As String = dtrow.Item(Form10.add_unit_txt.Text).ToString
                Dim St As String = dtrow.Item(Form10.add_state_txt.Text).ToString
                Dim Zip As String = StrConv(dtrow.Item(Form10.add_zip_txt.Text).ToString, VbStrConv.ProperCase)
                Dim City As String = StrConv(dtrow.Item(Form10.add_city_txt.Text).ToString, VbStrConv.ProperCase)
                Dim Words() As String = Split(City, "(")
                Dim revCity As String = Words(0)
                dtrow("NewAddress") = StrNum + " " + StrName + " " + UnitNum + " " + City + "," + " " + St + " " + Zip
                dtrow("NewListPrice") = Double.Parse(CStr(dtrow.Item(Form10.lp_txt.Text)))
                dtrow("NewListDate") = DateTime.Parse(CStr(dtrow.Item(Form10.ld_txt.Text)))
                dtrow("NewDateSold") = DateTime.Parse(CStr(dtrow.Item(Form10.DateSold_txt.Text)))
                dtrow("DaysMarket") = Integer.Parse(CStr(dtrow.Item(Form10.dom_txt.Text)))
                dtrow("SoldPrice") = Double.Parse(CStr(dtrow.Item(Form10.saleprice_txt.Text)))

            Next dtrow

---

Thank you

I would think that you'd be getting an exception on one of those Parse calls so that tells you exactly where the issue is.

That's why I would have thought also but I don't get the exception until I it loops through each column

What does the exception stack trace look like?

ok upon further observation I looked at all the data manually and have determined that one of the fields has a blank value if I place a value in it then it works fine obviously. So I need to be able to code this better to give the user feedback if and when there is a value that cant be parsed

I do not know how observe a stack trace but will try and research that

Thanks

Quote:

---

Originally Posted by billboy

ok upon further observation I looked at all the data manually and have determined that one of the fields has a blank value if I place a value in it then it works fine obviously. So I need to be able to code this better to give the user feedback if and when there is a value that cant be parsed

---

Thos Parse methods will throw an exception if the data cannot be parsed. You have two choices to handle invalid data:

1. Use a Try...Catch block to catch the exception thrown and then handle it gracefully.
2. Call TryParse instead, which will return False on failure rather than throw an exception.

Generally speaking, it is better to avoid exceptions if possible, so that suggests that option 2 is better. That said, in cases where an exception is rare and validation has an overhead, it may be preferable to use exception handling instead. To be honest, I'm not sure whether TryParse is slower than Parse and, if so, how much data you would have to be parsing for it to matter. You can time a couple of tests to find out if you like.

Quote:

---

Originally Posted by billboy

I do not know how observe a stack trace but will try and research that

---

Every exception has a StackTrace property that contains a representation of the call stack, i.e. the sequence of methods that had been called, when the exception was thrown. If you have the exception object in code then you can examine that property or it will be included in the output of the exception's ToString method. If you're looking at the Exception Assistant window then it shows all properties of the exception including the StackTrace.

Thanks for the quick feedback
I have very, very small amount of data so any performance issue would not even be noticeable.

I just began to implement TryParse method

Code:

---

If Not Integer.TryParse((CStr(dtrow.Item(Form10.dom_txt.Text))), Nothing) Then
                    MsgBox("You have a blank field")
                End If
                dtrow("DaysMarket") = Integer.Parse(CStr(dtrow.Item(Form10.dom_txt.Text)))

---

This seems to be a good way to go

any quick help on giving the user more informative feedback such as the line (row #) the error occurred on or perhaps the option to continue and have the program insert a zero or blank value?

You don't use TryParse and Parse. TryParse will parse the data if possible so it replaces Parse but adds validation too. E.g.

Code:

---

Dim value As Integer

If Integer.TryParse(text, value) Then
    row("Column") = value
Else
    MessageBox.Show("Invalid data: " & text)
End If

---

If you want to tell the user what line the issue was on then just keep a count of lines as you go. You can use a For loop instead to have an inbuilt line counter or just increment a variable. If you want to prompt the user to decide what to do then go ahead and do so. If you want to insert a "blank" value then use DBNull.Value, which is the .NET representation of a database NULL.

Thank you for pointing out the TryParse
I figured out how to get the row and return a field that will direct the user to which line has the problem

I like the DBNull.Value option

Thank you so much