aphillaura2000
Member
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
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