Solved

populate datagridview from text file, compute statistics

Posted on 2006-07-06
8
267 Views
Last Modified: 2008-01-09
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.OLEDB.4.0;Data Source=c:\temp;Extended Properties=""Text;HDR=NO;"""

        SQLString = "Select * from " & selectedfile

        Dim ConnectionText As New OleDb.OleDbConnection
        ConnectionText.ConnectionString = ConnectionString

        ConnectionText.Open()

        Dim AdapterText As New OleDb.OleDbDataAdapter(SQLString, ConnectionText)

        Dim DataSetText As New DataSet("TextFiles")
        AdapterText.Fill(DataSetText, "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.
0
Comment
Question by:dobbinjp
  • 5
  • 3
8 Comments
 
LVL 34

Expert Comment

by:Sancler
ID: 17053112
A DataGridView's .DataSource has to be a DataTable, not a DataSet.  So try

        DataGridView1.DataSource = DataSetText.Tables("TextFile")

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
0
 

Author Comment

by:dobbinjp
ID: 17053248
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.ShowDialog()
        'TextBox1.Text = OpenFileDialog1.FileName

        Dim ConnectionString As String
        Dim SQLString As String

        ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp;Extended Properties=""Text;HDR=NO;"""

        SQLString = "Select * from datafile.txt"

        Dim ConnectionText As New OleDb.OleDbConnection
        ConnectionText.ConnectionString = ConnectionString

        Dim AdapterText As New OleDb.OleDbDataAdapter(SQLString, ConnectionText)

        Dim DataSetText As New DataSet("TextFiles")
        AdapterText.Fill(DataSetText, "TextFiles")
        DataGridView1.DataSource = DataSetText.Tables("Textfiles")

    End Sub
'****

Thanks again.
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17053251
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
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 34

Expert Comment

by:Sancler
ID: 17053318
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
0
 

Author Comment

by:dobbinjp
ID: 17053467
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.OLEDB.4.0;Data Source=c:\temp\;Extended Properties=""text;HDR=No;FMT=Delimited"""

        SQLString = "Select * from datafile.txt"

        Dim ConnectionText As New OleDb.OleDbConnection
        ConnectionText.ConnectionString = ConnectionString

        Dim AdapterText As New OleDb.OleDbDataAdapter(SQLString, ConnectionText)

        Dim DataSetText As New DataSet("TextFiles")
        AdapterText.Fill(DataSetText, "TextFiles")
        DataGridView1.DataSource = DataSetText.Tables("TextFiles")

    End Sub
'****
0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 17053702
I can't see why you shouldn't be getting any display, and I haven't got time now to do a dummy run myself.  The only thing that looks a bit iffy to me is using the same name for the dataset as for one of the tables within it.  But that's style.  I doubt it would cause any operating problems.  The file looks OK.

To sort out whhere the problem is - no data getting to the datatable, or something wrong with the binding between the datatable and the datagridview - stick (temporarily)

        MsgBox(DataSetText.Tables("TextFiles").Rows.Count)

immediately after

        AdapterText.Fill(DataSetText, "TextFiles")

If it shows 0, or throws an error, then there's something wrong before display becomes an issue.  Otherwise, the problem is with the display.

I have to go out now.  I'll check in again when I'm back - a couple of hours.  Or, if you report back on the above msgbox, someone else may pick it up.

Roger
0
 

Author Comment

by:dobbinjp
ID: 17053793
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.......Handles 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!

0
 
LVL 34

Expert Comment

by:Sancler
ID: 17054810
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question