How can I speed up importing CSV files to Datagrid and creating new data

Joined
Nov 30, 2010
Messages
7
Location
Dallas, TX
Programming Experience
Beginner
Hi everyone,

I am a noobie and learning VB.net as I go. I have been working on a Windows form in (VB studio 2010 using Vb.net) which will load a CSV file into a Datagrid control and then create new data in the data grid based of the imported data. I have tinkered around and come up with the following code which works, but peformance really slows down when the CSV file contains more than 500 lines. The Typical CSV file that I intend to work with will have around 20,000 lines. Below is the code in my project (I have 2 data grids, 2 textbox, and 2 buttons on the form). I have thought about loading the CSV into an array and then performing any calculations on the data in the array rather than in the Datagrid control but I am running into problems getting that working.

Imports System.IO
Imports System.Data.OleDb

Public Class Form1
Dim Tablecount As Integer

Private Sub Import_CSV_Button_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Import_CSV_Button.Click
Dim i As Integer = 1
Dim fName As String = ""
OpenFileDialog1.InitialDirectory = "c:\temp\"
OpenFileDialog1.Filter = "CSV files (*.csv)|*.CSV"
OpenFileDialog1.FilterIndex = 2
OpenFileDialog1.RestoreDirectory = True

If (OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK) Then
fName = OpenFileDialog1.FileName
End If

Me.TextBox1.Text = fName
Dim TextLine As String = ""
Dim SplitLine() As String

If System.IO.File.Exists(fName) = True Then
Dim objReader As New System.IO.StreamReader(fName)
Do While objReader.Peek() <> -1
TextLine = objReader.ReadLine()
If i = 1 Then
Else
SplitLine = Split(TextLine, ",")
Me.DataGridView1.Rows.Add(SplitLine)
End If
i = i + 1
Loop
TextBox2.Text = i & " Lines Imported"
Tablecount = i - 2
objReader.Dispose()
Else
MsgBox("File Does Not Exist")
End If

End Sub

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
' 5 min populate
Dim I, J, K As Integer

For J = 11 To Tablecount
' Add interval to Table
Me.DataGridView1(2, J).Value = DataGridView1.Item(0, J - 11).Value & " - " & DataGridView1.Item(0, J).Value
' Add Hourly Sum to Table
Dim Sum As Double = 0
For K = 0 To 11
Sum = Sum + CDbl(DataGridView1.Item(1, J - K).Value)
Next K
Me.DataGridView1(3, J).Value = Format(Sum, "0.000")
Next J

Dim MinDate, MaxDate As DateTime
MinDate = Me.DataGridView1(0, 0).Value
MaxDate = Me.DataGridView1(0, Tablecount - 1).Value
Dim ts As TimeSpan = MaxDate.Subtract(MinDate)
Dim days As Integer = ts.Days
Dim StartDate As DateTime

StartDate = MinDate
For I = 0 To days
StartDate = StartDate.AddDays(I)
DataGridView2.Rows.Add(New String() {Format(StartDate, "MM/dd/yy"), Format(StartDate, "ddd")})
Next I
End Sub

End Class

Any help or pointers would be greatly appreciated.

thanks,

AP
 
Joined
Nov 30, 2010
Messages
7
Location
Dallas, TX
Programming Experience
Beginner
I have found a work around. Looks like Paging and virtual mode is the way to go. Just a FYI for anyone who runs into this problem. Will update once I get that figured out
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
Youre pushing data into and out of a datagridview, which is never a good way to go..
You should be using TextFieldParser to read your CSV, and loading it into a typed datatable directly:

VB.NET:
        Using myreader As New Microsoft.VisualBasic.FileIO.TextFieldParser(accts)
                myreader.TextFieldType = FileIO.FieldType.Delimited
                myreader.Delimiters = New String() {","}
                Dim f As String()
                While Not myreader.EndOfData
                    Try
                        f = myreader.ReadFields()

                        Dim ro as MyTableRow = _myDataSet.MyTable.NewMyTableRow()
                        ro.Name = f(0)
                        ro.Age = Integer.Parse(f(1))
                        ro.DateOfBirth = DateTime.ParseExact(f(2), "yyyyMMdd")
                        _myDataSet.MyTable.AddMyTableRow(ro)


                    Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                        MessageBox.Show(ex.Message)
                    End Try
                End While
            End Using
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,469
Location
Norway
Programming Experience
10+
Actually you can get most of the code cjard posted by inserting the snippet "read a delimited text file" (insert snippet: Fundamentals : file system). Look into using snippets, they can save you from writing much common code.
 

J Trahair

Well-known member
Joined
May 14, 2008
Messages
175
Location
Spain
Programming Experience
10+
I found that adding a new row to the datagrid every time you add new data slows things down considerably. I would add say 1,000 blank rows then add another 1,000 when the row being written to is less than 5 rows from the end, etc. Any unused blank rows you don't want at the bottom can be removed. Unbound only, of course.
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,469
Location
Norway
Programming Experience
10+
I found that adding a new row to the datagrid every time you add new data slows things down considerably. I would add say 1,000 blank rows then add another 1,000 when the row being written to is less than 5 rows from the end, etc. Any unused blank rows you don't want at the bottom can be removed. Unbound only, of course.
I'm with cjard on that, manipulate the data source in memory only, bind it to UI afterwards, or if it is and needs to be bound during changes I'd prevent updating the UI until operation was done (for example BindingSource.RaiseListChangedEvents).
 
Top Bottom