TextFieldParser CreateDataTable

[billboy]

Ok so I am taking jm advice and using TextField Parser instead of ADO.net to read my delimited files and then build my own datatables

First question is how would I query the data?

I need to be able to have two tables, one with sold propertys and one with propertys still for sale.

The table I created thus far has all the records, the problem and my second question is how do you allow for a null value ?

If one of the records isnt Sold then it won't have a Selling Price therefore when I set my datacolumn to be of System.Integer type It is not accepted

Here is what I have so far:

Private Sub FileToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FileToolStripMenuItem.Click
        Dim openFileDialog1 As New OpenFileDialog

        If openFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
            Using myReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(openFileDialog1.FileName)
                myReader.SetDelimiters(",")
                Dim currentRow As String()
                Dim currentField As String
                Dim i As Integer = -1

                currentRow = myReader.ReadFields()

                For Each currentField In currentRow
                    i += 1
                    
                    DataGridView1.Columns.Add("Field", currentRow(i))

                Next
                Dim table As New DataTable

                With table.Columns
                    .Add("StreetNumber", System.Type.GetType("System.String"))
                    .Add("StreetName", System.Type.GetType("System.String"))
                    .Add("Unit Number", System.Type.GetType("System.String"))
                    .Add("City", System.Type.GetType("System.String"))
                    .Add("State", System.Type.GetType("System.String"))
                    .Add("ZipCode", System.Type.GetType("System.String"))
                    .Add("MLS Number", System.Type.GetType("System.String"))
                    .Add("OriginalListPrice", System.Type.GetType("System.String"))
                    .Add("CurrentListPrice", System.Type.GetType("System.String"))
                    .Add("ListingDate", System.Type.GetType("System.DateTime"))
                    .Add("DayOnMarket", System.Type.GetType("System.Double"))
                    .Add("Status", System.Type.GetType("System.String"))
                    .Add("Style", System.Type.GetType("System.String"))
                    .Add("Bedrooms", System.Type.GetType("System.String"))
                    .Add("Baths", System.Type.GetType("System.String"))
                    .Add("SqFt", System.Type.GetType("System.String"))
                    .Add("GarageSpaces", System.Type.GetType("System.String"))
                    .Add("Fireplace", System.Type.GetType("System.String"))
                    .Add("Pool", System.Type.GetType("System.String"))
                    .Add("View", System.Type.GetType("System.String"))
                    .Add("LotSize", System.Type.GetType("System.String"))
                    .Add("YearBuilt", System.Type.GetType("System.String"))
                    .Add("Levels", System.Type.GetType("System.String"))
                    .Add("Attached", System.Type.GetType("System.String"))
                    .Add("SaleType", System.Type.GetType("System.String"))
                    .Add("Longitude", System.Type.GetType("System.String"))
                    .Add("Latitude", System.Type.GetType("System.String"))
                    .Add("HOAFees", System.Type.GetType("System.String"))
                    .Add("AgentRemarks", System.Type.GetType("System.String"))
                    .Add("PropertyDescription", System.Type.GetType("System.String"))
                    .Add("DateSold", System.Type.GetType("System.String"))
                    .Add("SellingPrice", System.Type.GetType("System.String"))

                    DataGridView2.DataSource = table


                End With
                While Not myReader.EndOfData
                    Try
                        currentRow = myReader.ReadFields()
                        DataGridView1.Rows.Add(currentRow)
                        table.Rows.Add(currentRow)
                        For Each currentField In currentRow

                        Next

                    Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                        MsgBox("Line " & ex.Message & _
                        "is not valid and will be skipped.")
                    End Try
                End While
            End Using

        Else
            'The user closed the dialog without choosing a file
        End If


    End Sub

The datagridviews are only for me to see my results

[jmcilhinney]

First, I would suggest that you change this sort of thing:

.Add("SellingPrice", System.Type.GetType("System.String"))

to the simpler:

.Add("SellingPrice", GetType(String))

With regards to the second question, you use DBNull.Value to represent NULL in a DataTable, e.g.

myDataRow("SomeColumn") = If(String.IsNullOrEmpty(someText), DBNull.Value, CInt(someText))

With regards to the first question, you have various options.

1. You could create two identical DataTables and then add your records to one or the other depending on the value of one of the columns.
2. You could just use one DataTable for all the data and create two DataViews with the appropriate RowFilter values set, then access the data via the DataViews.
3. You could do as for option 2 and then call ToTable on the two DataViews to create two new DataTables.

[stanav]

I would also suggest not to use string type for everything. For example, DateSold should be typed Date and SellingPrice should be Decimal.

[billboy]

I would also suggest not to use string type for everything. For example, DateSold should be typed Date and SellingPrice should be Decimal.

Thanks I got that part, I only had everything set to string temporarily to see if I was getting the data properly, since I have the null issue when when of the rows does not contain a value for a column

[jmcilhinney]

I would also suggest not to use string type for everything. For example, DateSold should be typed Date and SellingPrice should be Decimal.

Quite so. I didn't notice that but you should definitely do so. You will obviously be reading everything as strings from the file, but you will then do the appropriate conversions before loading the data into the table, generally using TryParse methods. What you do if a conversion fails is up to you. You might reject the whole file, you might prompt the user to specify a valid value or you might use a default value, e.g. NULL.

There's also the option of creating a typed DataSet and building your table schema in the designer. That will make your table somewhat simpler to deal with in code.

[billboy]

Thats was the whole idea of going with the textfield parser and creating my own tables, per your suggestion as an idea for my project oppossed to ADO and schema and such

Is your suggestion on the GetType for simplicity purposes or is there another reason, it is simpler obviously just want to learn if there was another reason?

Also I am struggling with this:

myDataRow("SomeColumn") = If(String.IsNullOrEmpty(someText), DBNull.Value, CInt(someText))

I think the first part is looking at a row and column position in a table? in my case

currentrow("SellingPrice") ?

I am dont understand what (sometext) is

[jmcilhinney]

Thats was the whole idea of going with the textfield parser and creating my own tables, per your suggestion as an idea for my project oppossed to ADO and schema and such

You're still using DataTables regardless. The difference is that you are constructing the DataTable(s) yourself and populating it yourself, rather than letting ADO.NET do it for you. You can still do that whether you construct the DataTable in code or in the designer.

Is your suggestion on the GetType for simplicity purposes or is there another reason, it is simpler obviously just want to learn if there was another reason?

Just for simplicity. I'd only use the other option if you must use a String to specify the type rather than an actual data type, like if that String is coming from some external source.

Also I am struggling with this:

myDataRow("SomeColumn") = If(String.IsNullOrEmpty(someText), DBNull.Value, CInt(someText))

I think the first part is looking at a row and column position in a table? in my case

currentrow("SellingPrice") ?

I am dont understand what (sometext) is

'someText' is, as the name suggests, some text. In your case it's the text that your TextFieldParser read from the the file. That code says that you first check whether your text is Nothing or an empty String or it has a value. If it has a value then you convert that value to an Integer, otherwise use DNBull.Value. Whatever the result, assign that value to the SellingPrice column of the current DataRow, which you'll be adding to your DataTable once the whole row has been read, processed and populated. You'd do something similar for each field before adding the row.

[billboy]

Can you help me see how to get the right field??

currentRow("SellingPrice") = If(String.IsNullOrEmpty(""), DBNull.Value, CInt(currentField))

also I recently installed vb2008 express and when you get the blue squirrley lines letting you know ther eis a problem in your code when you hover your mouse it gives you some information, I am not getting that information is there a setting somewhere I have looked all over
Thanks

[jmcilhinney]

What is the point of that line of code? Ask yourself what it's actually supposed to be doing, then look at it and ask yourself if it actually does that.

[billboy]

What is the point of that line of code? Ask yourself what it's actually supposed to be doing, then look at it and ask yourself if it actually does that.

The code as I understand it is to look at the current rows column value that is "SellingPrice" and if the value for that column is empty or null it will put nothing "" otherwise it will convert the value to an integer

This part is where I am lost:

DBNull.Value, CInt(currentField))

No it doesnt actually do it, it wont even compile the way I have it written

I tried changing it
Dim SP as String
SP = myReader.ReadFields(31)

currentRow("SellingPrice") = If(String.IsNullOrEmpty(""), DBNull.Value, CInt(SP))

[jmcilhinney]

No, that is not what the code is doing. This part:

look at the current rows column value that is "SellingPrice" and if the value for that column is empty or null

is correct, but that's not what you're doing. This is your code:

If(String.IsNullOrEmpty(""),

How is that checking the value of the SellingPrice field if the current row? That is testing an empty String to see if it's null or empty. Of course an empty String is empty, but that's not what you're supposed to be testing anyway. As you just said yourself, you're supposed to be testing whether the SellingPrice field of the current row is null or empty.

Further, this:

if the value for that column is empty or null it will put nothing ""

is is wrong. It's supposed to be testing for an empty String and, if it finds one, it inserts a null value, i.e. DBNull.Value, into the DataRow.

It's very simple: read a line from the file, test each field and, for each one, if you read a value then convert that value to the appropriate type and insert it into the DataRow, otherwise insert NULL into the DataRow.

So, think about the steps first, BEFORE you write the code. If you have a clear picture of what thye code is supposed to do, then you can more likely write code to do it.

1. Read a line of the file.
2. For each value read from the file, test the value.
3. If there is a value, convert it to the appropriate type and insert it into the appropriate field of the DataRow.
4. If there is no value, insert NULL into the DataRow.

For one clue, look at the code I posted some time ago:

myDataRow("SomeColumn") = If(String.IsNullOrEmpty(someText), DBNull.Value, CInt(someText))

It's no coincidence that those two values are the same. That is code, so 'someText' is a variable. As I have stated in words, you test the value and, if there is one, you convert that same value. If you're using the same value twice then obviously you use the same variable twice in code, which you aren't doing.

[jmcilhinney]

To speed things up a bit, here's an example that reads name and date of birth from a text file into a DataTable:

Dim table As New DataTable
 
With table.Columns
    .Add("Name", GetType(String))
    .Add("DateOfBirth", GetType(Date))
End With
 
Using reader As New TextFieldParser("file path here")
    Do Until reader.EndOfData
        Dim row = table.NewRow()
        Dim fields = reader.ReadFields()
        Dim name = fields(0)
        Dim dateOfBirth = fields(1)
 
        row("Name") = If(String.IsNullOrEmpty(name), CObj(DBNull.Value), name)
        row("DateOfBirth") = If(String.IsNullOrEmpty(dateOfBirth), CObj(DBNull.Value), CDate(dateOfBirth))
 
        table.Rows.Add(row)
    Loop
End Using

Now, this assumes that all rows that have a value for date of birth have a valid value. That's probably not a safe assumption, so you should probably use TryParse or TryParseExact for any conversion. What you do if a non-null value fails to convert is up to you. You might just insert a null or you might fail the whole import. It depends on what the requirements are for your app.

[billboy]

ok you beat me to it I think I am on the right track though??

While Not myReader.EndOfData
Try
currentRow = myReader.ReadFields()
Dim sp As String
sp = myReader.ReadFields(31)
currentRow("SellingPrice") = If(String.IsNullOrEmpty(sp), DBNull.Value, CInt(sp))
table.Rows.Add(currentRow)

currentRow("SellingPrice") = If(String.IsNullOrEmpty(sp), DBNull.Value, CInt(sp))

I am getting an eror message:

Cannot infer a common type for the second and third operands of the 'If' operator. One must have a widening conversion to the other.


So I added the CObj before the DbNull and no more error

[jmcilhinney]

If only I had posted code that worked so that you could refer to it. Hang on... I did!

[billboy]

If only I had posted code that worked so that you could refer to it. Hang on... I did!

LOL, I had already come up with the line of code before you posted
read my edit, I then followed your code and it worked

Thanks for all your patience, you should start on on-line learning program you are really good at it.

Ok now teacher, You said in previous post :

if the value for that column is empty or null it will put nothing ""

is is wrong. It's supposed to be testing for an empty String and, if it finds one, it inserts a null value, i.e. DBNull.Value, into the DataRow.

Now you saying this code assumes field has valid data? My empty fields are not converting? I thought it would put a null value if the field was empty?

Now I know you are right and I am wrong so I am not understanding something

[jmcilhinney]

You're going to have to be more specific. That code WILL insert NULL values for empty fields. If a field is not empty then it will use the value, possibly converting it to another type. EXACTLY what is happening and EXACTLY what data is it happening to?

[billboy]

Here is what I get:

Input string was not in a correct format.Couldn't store <> in SellingPrice Column. Expected type is Double.

[jmcilhinney]

Is "<>" empty? Would you really expect String.IsNullOrEmpty to return True for "<>"? If "<>" means "no value" to your application then you'll have to code that specifically, because that's a special case.

What's more, that error message suggests that you're not actually converting the string to a number, because it should have got as far as trying to store the value in the column if the data wasn't valid.

Finally, if the data type of the SellingPrice column is Double, why are you converting the data from the file into an Integer in the last code you posted?

[billboy]

Is "<>" empty? Would you really expect String.IsNullOrEmpty to return True for "<>"? If "<>" means "no value" to your application then you'll have to code that specifically, because that's a special case.

What's more, that error message suggests that you're not actually converting the string to a number, because it should have got as far as trying to store the value in the column if the data wasn't valid.

Finally, if the data type of the SellingPrice column is Double, why are you converting the data from the file into an Integer in the last code you posted?

The error message says "<>" , the fields are empty

I changed the code to Double because when i use integer I go the following error:

Input string was not in a correct format.Couldn't store <597,000> in SellingPrice Column. Expected type is Int32.

[jmcilhinney]

The code you have works exactly as it should. You must have some other code that is failing. As I said earlier, the error message you're getting indicates that you are trying to insert the text from the file directly into the DataRow without converting it. The code you've posted does convert the text so you must have some other code somewhere that doesn't convert and doesn't check for nulls.

I just create a file containing the following data:

Peter,123.456
Paul,
,987.654
,
Mary,123.987

I then ran this code:

Imports Microsoft.VisualBasic.FileIO
 
Public Class Form1
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            Dim table As New DataTable
 
            With table.Columns
                .Add("Name", GetType(String))
                .Add("Value", GetType(Double))
            End With
 
            Dim filePath As String = IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "Test.csv")
 
            Using reader As New TextFieldParser(filePath)
                reader.Delimiters = New String() {","}
 
                Do Until reader.EndOfData
                    Dim fields As String() = reader.ReadFields()
                    Dim name As String = fields(0)
                    Dim value As String = fields(1)
                    Dim row As DataRow = table.NewRow()
 
                    row("Name") = If(String.IsNullOrEmpty(name), CObj(DBNull.Value), name)
                    row("Value") = If(String.IsNullOrEmpty(value), CObj(DBNull.Value), CDbl(value))
 
                    table.Rows.Add(row)
                Loop
            End Using
 
            Me.DataGridView1.DataSource = table
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub
 
End Class

and it ran exactly as it should. Empty values were inserted as NULLs and numbers were converted correctly, exactly as I've been saying they would. I then changed the code to this:

Imports Microsoft.VisualBasic.FileIO
 
Public Class Form1
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            Dim table As New DataTable
 
            With table.Columns
                .Add("Name", GetType(String))
                .Add("Value", GetType(Double))
            End With
 
            Dim filePath As String = IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "Test.csv")
 
            Using reader As New TextFieldParser(filePath)
                reader.Delimiters = New String() {","}
 
                Do Until reader.EndOfData
                    Dim fields As String() = reader.ReadFields()
                    Dim name As String = fields(0)
                    Dim value As String = fields(1)
                    Dim row As DataRow = table.NewRow()
 
                    row("Name") = name
                    row("Value") = value
                    'row("Name") = If(String.IsNullOrEmpty(name), CObj(DBNull.Value), name)
                    'row("Value") = If(String.IsNullOrEmpty(value), CObj(DBNull.Value), CDbl(value))
 
                    table.Rows.Add(row)
                Loop
            End Using
 
            Me.DataGridView1.DataSource = table
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub
 
End Class

and I got the error message you're saying you got. If you what I have been saying all along then it will work. If you don't then it won't. I'm afraid that this will be my last post because I feel that I'm just repeating myself over and over and you're just not doing what I'm saying.

[billboy]

I am sorry to have fustrated you, sometimes when you focusing on a problem its not always easy to see what someone else can , maybe that doesnt happen to you.

I removed some code I had and that seems to have done the trick, although i cant tell for sure, I am now getting an out of idex error which after debugging for a bit seems to be being casued by "," in a text field

I am going to google and see what i can come up with, any adive on that matter would of course be appreciated as always but i understand if you no longer want to post

Thanks again I have learned quite a bit