Help reading from csv file!

[bbesase]

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

[kebo]

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

[MetalInquisitor]

Check the documentation for the String.Split method.

[TysonLPrice]

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

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

[jmcilhinney]

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

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.

[TysonLPrice]

I missed that. Here is another link. I did not test this one:

http://www.snippetbank.net/detail/sn...aGridView.html

[billboy]

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

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

The you can read your table into the DGV

DataGridView1.DataSource = table

[bbesase]

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.

[jmcilhinney]

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.

[billboy]

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

[bbesase]

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.

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

[jmcilhinney]

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:

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

Using reader As New TextFieldParser(filePath) With {.Delimiters = {","}}
    Do Until reader.EndOfData
        Dim fields = reader.ReadFields()

        'Use fields here.
    Loop
End Using

For Each line In File.ReadLines(filePath)
    Dim fields = line.Split(","c)

    'Use fields here.
Next

For Each fields In File.ReadLines(filePath).Select(Function(s) s.Split(","c))
    'Use fields here.
Next

[bbesase]

Ah! Thank you so much for your help! It works finally!