Printable View
I'm creating a program that reads values from a csv file and then displays them into a datagrid. The only problem is I have no clue how to do either of the two tasks, any help would be greatly appreciated.
.txt file is like this
John Doe, 15,17,15,18,19
John Deer, 18,16,82,16,19
I need each csv to be shown on each corresponding column, and I'm lost... :(
Thank you in advance
Here is a good starting place. Once you have the data in a data table, you can bind it to a datagrid.
Please read it to understand what is going on and if you still have problems, come back with the code you have tried.
kevin
Check the documentation for the String.Split method.
You can find this here. Make sure you really look at what it is doing:
http://blueraiden.exofire.net/blog/i...w-using-vb-net
Code:Imports System.Data.SqlClient
Public Class Form1
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim ExcelDataSet As System.Data.DataSet
Dim ExcelAdapter As System.Data.OleDb.OleDbDataAdapter
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles Button1.Click
MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; " + _
"Data Source=C:/TestIt.xlsx;Extended Properties=Excel 12.0;")
Try
ExcelAdapter = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
ExcelAdapter.TableMappings.Add("Table", "Excel Data")
ExcelDataSet = New System.Data.DataSet
ExcelAdapter.Fill(ExcelDataSet)
DataGridView1.DataSource = ExcelDataSet.Tables(0)
MyConnection.Close()
Catch ex As Exception
MessageBox.Show("Error: " + ex.ToString, "Importing Excel", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
End Class
That's for an XLSX file rather than a CSV. You can use ADO.NET to read a CSV similarly, although some of the details will be different. You can find the appropriate connection string at www.connectionstrings.com. Note that you treat the folder like a database and each file as a table.Quote:
Originally Posted by TysonLPrice
I missed that. Here is another link. I did not test this one:
http://www.snippetbank.net/detail/sn...aGridView.html
Here is some code to read the file using TextfieldParser and create a DataTable using the openFiledialog to select the file you want to read
The you can read your table into the DGVCode:dim table as New DataTable
Public Sub ReadData()
OpenFileDialog1.InitialDirectory = "C:\"
OpenFileDialog1.Filter = "Text Files (*.txt)|*.txt|CSV Files (*.csv)|*.csv"
With OpenFileDialog1
.FileName = String.Empty
If .ShowDialog() = Windows.Forms.DialogResult.OK Then
Dim safefilename As String = .FileName
Using myReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(safefilename)
myReader.SetDelimiters(vbTab)
Dim currentRow As String()
currentRow = myReader.ReadFields()
Dim colNameList As New List(Of String)
Dim colName As String = String.Empty
For i As Integer = 0 To currentRow.GetUpperBound(0)
colName = currentRow(i)
Dim suffix As Integer = 1
While colNameList.Contains(colName)
colName = currentRow(i) & suffix.ToString
suffix += 1
End While
colNameList.Add(colName)
Next
For Each currentField As String In colNameList
table.Columns.Add(currentField, GetType(System.String))
Next
While Not myReader.EndOfData
Try
currentRow = myReader.ReadFields()
table.Rows.Add(currentRow)
Catch ex As Exception
End Try
End While
End Using
End If
End With
End Sub
Code:DataGridView1.DataSource = table
Thanks guys. Except when I read in the lines, I don't know how to separate the values. I have multiple lines with 6 + values in each line, and I need to only read in the first 6 values. I dont need any more of the values exceeding that. I'm a noob at programming and have no clue what to do.
Well, you've already been given three ways to do it. Using ADO.NET, i.e. an OleDbDataAdapter, will read all the data straight into a DataTable so all the data is already split into rows and columns. If you use a StreamReader and its ReadLine method or File.ReadAllLines or .ReadLines then you'll call String.Split to split each line. If you use a TextFieldParser then the ReadFields method reads a line and splits it in one go. I suggest that you spend a bit more time studying the information already provided because you already have everything you need.Quote:
Originally Posted by bbesase
Like jmcilhinney said, you have all the parts in multiple ways of doing what you need. As someone who is still learning myself I would take his suggestion and rather then just try to get what you need working analyze the code you have and ask what each line is doing you will not only find your answer but much better off for it
After doing more research, I have this code and it finally works! However, now the only problem is that when it runs through the program, it reads through the .txt twice and I'm not sure how to fix it, I think it has something to do with the loops and what not, but from what I've tried its failed. Here is the code.
Code:Public Sub readThrough()
Try
Catch ex As Exception
End Try
Dim dt As New DataTable
Dim r As DataRow
dt.Columns.Add("Golfer Name")
dt.Columns.Add("Quota 1")
dt.Columns.Add("Quota 2")
dt.Columns.Add("Quota 3")
dt.Columns.Add("Quota 4")
dt.Columns.Add("Quota 5")
dt.Columns.Add("Average Quota")
Dim sr As New StreamReader("C:\Users\Brent\Documents\Visual Studio 2010\Projects\Golf\Golf\Quota.txt")
Dim line As String = sr.ReadLine()
Do While Not line Is Nothing
'Dim filename1 As String = Application.StartupPath & "\temp.000"
Dim filename1 As String = "C:\Users\Brent\Documents\Visual Studio 2010\Projects\Golf\Golf\Quota.txt"
Dim fields As String()
Dim delimiter As String = ","
Using parser As New TextFieldParser(filename1)
parser.SetDelimiters(delimiter)
While Not parser.EndOfData
' Read in the fields for the current line
fields = parser.ReadFields()
r = dt.NewRow
r("Golfer Name") = fields(0)
r("Quota 1") = fields(1)
r("Quota 2") = fields(2)
r("Quota 3") = fields(3)
r("Quota 4") = fields(4)
r("Quota 5") = fields(5)
r("Average Quota") = CStr(CInt(fields(1)) + CInt(fields(2)) + CInt(fields(3)) + CInt(fields(4)) + CInt(fields(5))) / 4
dt.Rows.Add(r)
End While
End Using
line = sr.ReadLine()
Loop
sr.Close()
dgv1.DataSource = dt
End Sub
When you create a StreamReader you are opening the file for reading. When you create a TextFieldParser you are opening the file for reading. A TextFieldParser actually creates a StreamReader internally. You do not use both. You use one or the other. You either create a StreamReader and call ReadLine in a loop or you create a TextFieldParser and call ReadFields in a loop. If you're using .NET 4.0 or later and thinking of going the StreamReader route, I'd go with File.ReadLines instead:Code:Using reader As New StreamReader(filePath)
Do Until reader.EndOfStream
Dim line = reader.ReadLine()
Dim fields = line.Split(","c)
'Use fields here.
Loop
End Using
Code:Using reader As New TextFieldParser(filePath) With {.Delimiters = {","}}
Do Until reader.EndOfData
Dim fields = reader.ReadFields()
'Use fields here.
Loop
End Using
Code:For Each line In File.ReadLines(filePath)
Dim fields = line.Split(","c)
'Use fields here.
Next
Code:For Each fields In File.ReadLines(filePath).Select(Function(s) s.Split(","c))
'Use fields here.
Next
Ah! Thank you so much for your help! It works finally!