-
May 15th, 2014, 08:46 PM
#1
Thread Starter
Frenzied Member
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
-
May 15th, 2014, 10:58 PM
#2
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.
-
May 16th, 2014, 12:27 AM
#3
Thread Starter
Frenzied Member
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
-
May 16th, 2014, 12:33 AM
#4
Re: Adding Columns Getting Type
What does the exception stack trace look like?
-
May 16th, 2014, 12:55 AM
#5
Thread Starter
Frenzied Member
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
-
May 16th, 2014, 01:17 AM
#6
Re: Adding Columns Getting Type
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.
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.
-
May 16th, 2014, 01:30 AM
#7
Thread Starter
Frenzied Member
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?
-
May 16th, 2014, 01:59 AM
#8
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.
-
May 16th, 2014, 02:10 AM
#9
Thread Starter
Frenzied Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|