Results 1 to 9 of 9

Thread: Adding Columns Getting Type

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2009
    Location
    Los Angeles
    Posts
    1,335

    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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Adding Columns Getting Type

    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.

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2009
    Location
    Los Angeles
    Posts
    1,335

    Re: Adding Columns Getting Type

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

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Adding Columns Getting Type

    What does the exception stack trace look like?

  5. #5

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2009
    Location
    Los Angeles
    Posts
    1,335

    Re: Adding Columns Getting Type

    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

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Adding Columns Getting Type

    Quote Originally Posted by billboy View Post
    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 View Post
    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.

  7. #7

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2009
    Location
    Los Angeles
    Posts
    1,335

    Re: Adding Columns Getting Type

    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?

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Adding Columns Getting Type

    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.

  9. #9

    Thread Starter
    Frenzied Member
    Join Date
    Aug 2009
    Location
    Los Angeles
    Posts
    1,335

    Re: Adding Columns Getting Type

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width