dobbinjp
asked on
populate datagridview from text file, compute statistics
I am a newbie to VB .NET and have a project where I need to programmatically read comma-delimited text files (generated once every 5 minutes), perform some calculations on the data columns before sending some of the returned values to another application. As an intermediate step and to learn more about the process, I am trying to enable the end user to select a text file, populate the datagridview with the file's data and display some calculations on the same form (in textboxes...sum, average, etc.). I can populate and view the data for one file manually, but I cannot seem to get the data to display in the datagridview programmatically, based on the user-selected file.
The code I have so far is as follows:
'****
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
OpenFileDialog1.ShowDialog ()
TextBox1.Text = OpenFileDialog1.FileName
Dim selectedfile As String = OpenFileDialog1.FileName
Dim ConnectionString As String
Dim SQLString As String
ConnectionString = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=c:\temp;Extended Properties=""Text;HDR=NO;" ""
SQLString = "Select * from " & selectedfile
Dim ConnectionText As New OleDb.OleDbConnection
ConnectionText.ConnectionS tring = ConnectionString
ConnectionText.Open()
Dim AdapterText As New OleDb.OleDbDataAdapter(SQL String, ConnectionText)
Dim DataSetText As New DataSet("TextFiles")
AdapterText.Fill(DataSetTe xt, "TextFile")
DataGridView1.DataSource = DataSetText
ConnectionText.Close()
End Sub
'*****
Thanks for your assistance...I think if I get some help getting the datagridview populated and the summary statistics calculated I should be good to go.
The code I have so far is as follows:
'****
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
OpenFileDialog1.ShowDialog
TextBox1.Text = OpenFileDialog1.FileName
Dim selectedfile As String = OpenFileDialog1.FileName
Dim ConnectionString As String
Dim SQLString As String
ConnectionString = "Provider=Microsoft.Jet.OL
SQLString = "Select * from " & selectedfile
Dim ConnectionText As New OleDb.OleDbConnection
ConnectionText.ConnectionS
ConnectionText.Open()
Dim AdapterText As New OleDb.OleDbDataAdapter(SQL
Dim DataSetText As New DataSet("TextFiles")
AdapterText.Fill(DataSetTe
DataGridView1.DataSource = DataSetText
ConnectionText.Close()
End Sub
'*****
Thanks for your assistance...I think if I get some help getting the datagridview populated and the summary statistics calculated I should be good to go.
ASKER
Roger,
Thanks for the quick response. I modified my code to explicitly grab the file, "c:\temp\datafile.txt" (make this as simple as possible), eliminated the connection open/close and added the tables reference i/o the dataset. I also noticed my table naming convention was inconsistent ("TextFile" in one place, "TextFiles" in another). However, it still didn't populate the existing datagrid1 in my form.
I assume once we get this to display, the fields will be F1, F2, etc. It would be great to name the fields "id", "somefield1", etc. Does anyone have a code snippet to name these?
My code looks like the following now:
'****
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'OpenFileDialog1.ShowDialo g()
'TextBox1.Text = OpenFileDialog1.FileName
Dim ConnectionString As String
Dim SQLString As String
ConnectionString = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=c:\temp;Extended Properties=""Text;HDR=NO;" ""
SQLString = "Select * from datafile.txt"
Dim ConnectionText As New OleDb.OleDbConnection
ConnectionText.ConnectionS tring = ConnectionString
Dim AdapterText As New OleDb.OleDbDataAdapter(SQL String, ConnectionText)
Dim DataSetText As New DataSet("TextFiles")
AdapterText.Fill(DataSetTe xt, "TextFiles")
DataGridView1.DataSource = DataSetText.Tables("Textfi les")
End Sub
'****
Thanks again.
Thanks for the quick response. I modified my code to explicitly grab the file, "c:\temp\datafile.txt" (make this as simple as possible), eliminated the connection open/close and added the tables reference i/o the dataset. I also noticed my table naming convention was inconsistent ("TextFile" in one place, "TextFiles" in another). However, it still didn't populate the existing datagrid1 in my form.
I assume once we get this to display, the fields will be F1, F2, etc. It would be great to name the fields "id", "somefield1", etc. Does anyone have a code snippet to name these?
My code looks like the following now:
'****
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'OpenFileDialog1.ShowDialo
'TextBox1.Text = OpenFileDialog1.FileName
Dim ConnectionString As String
Dim SQLString As String
ConnectionString = "Provider=Microsoft.Jet.OL
SQLString = "Select * from datafile.txt"
Dim ConnectionText As New OleDb.OleDbConnection
ConnectionText.ConnectionS
Dim AdapterText As New OleDb.OleDbDataAdapter(SQL
Dim DataSetText As New DataSet("TextFiles")
AdapterText.Fill(DataSetTe
DataGridView1.DataSource = DataSetText.Tables("Textfi
End Sub
'****
Thanks again.
Sorry, I missed the bit about "and the summary statistics calculated ..."
I think this is what you want for that.
http://msdn2.microsoft.com/en-us/library/system.data.datatable.compute.aspx
Roger
I think this is what you want for that.
http://msdn2.microsoft.com/en-us/library/system.data.datatable.compute.aspx
Roger
Cross-posting on that last. I didn't check the connection string. You will need to add
FMT=Delimited
to the end of your Extended Properties - after the other bits, within the double-double quotes.
Roger
FMT=Delimited
to the end of your Extended Properties - after the other bits, within the double-double quotes.
Roger
ASKER
Roger,
Thanks again. The link on summary statistics is just what I need and I fixed the connection string. Still no data display in the datagridview. though.
I thought it might help if you could look at the text file itself. It may be downloaded at http://transp20.vuse.vanderbilt.edu/vector/datafile.txt
I won't be extracting all fields from it, just a few, but I am doing "select *" for now until I get the data to display successfully.
My code now looks like:
'****
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim ConnectionString As String
Dim SQLString As String
ConnectionString = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=c:\temp\;Extended Properties=""text;HDR=No;F MT=Delimit ed"""
SQLString = "Select * from datafile.txt"
Dim ConnectionText As New OleDb.OleDbConnection
ConnectionText.ConnectionS tring = ConnectionString
Dim AdapterText As New OleDb.OleDbDataAdapter(SQL String, ConnectionText)
Dim DataSetText As New DataSet("TextFiles")
AdapterText.Fill(DataSetTe xt, "TextFiles")
DataGridView1.DataSource = DataSetText.Tables("TextFi les")
End Sub
'****
Thanks again. The link on summary statistics is just what I need and I fixed the connection string. Still no data display in the datagridview. though.
I thought it might help if you could look at the text file itself. It may be downloaded at http://transp20.vuse.vanderbilt.edu/vector/datafile.txt
I won't be extracting all fields from it, just a few, but I am doing "select *" for now until I get the data to display successfully.
My code now looks like:
'****
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim ConnectionString As String
Dim SQLString As String
ConnectionString = "Provider=Microsoft.Jet.OL
SQLString = "Select * from datafile.txt"
Dim ConnectionText As New OleDb.OleDbConnection
ConnectionText.ConnectionS
Dim AdapterText As New OleDb.OleDbDataAdapter(SQL
Dim DataSetText As New DataSet("TextFiles")
AdapterText.Fill(DataSetTe
DataGridView1.DataSource = DataSetText.Tables("TextFi
End Sub
'****
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is embarassing...there was nothing wrong with the code, it was all pilot error.
The button I was clicking was "button1" and the first line read:
"Private Sub Button1_Click.......Handle s Button2.Click"
Boy, do I feel dumb! Once I fixed that the data displayed beautifully. I will post final results for future reference...I think I have all the information I need to average the numbers, etc. and rename the columns.
Thanks!
The button I was clicking was "button1" and the first line read:
"Private Sub Button1_Click.......Handle
Boy, do I feel dumb! Once I fixed that the data displayed beautifully. I will post final results for future reference...I think I have all the information I need to average the numbers, etc. and rename the columns.
Thanks!
No need to be embarassed. It happens, and things like that are often the most difficult to spot. But so often, when that sort of thing happens, the thread just dies. Thanks for letting me know. And for the points.
Roger
Roger
DataGridView1.DataSource = DataSetText.Tables("TextFi
And, incidentally, you don't need explicitly to open and close the connection when you are using a DataAdapter: it does it automatically itself.
Roger