Wednesday, August 04, 2010

How to load csv file into a datagrid in VB.net

Goal:
Load a csv file of any dimension into a datagridview. This example reads a csv file and adds rows and columns to the datagrid as needed. You can specify your own delimiter ("," or ";" or whatever).


Instructions:
Open a new Windows Forms Application and add 1 button, an openfile dialog, a datagridview and a richtextbox to the form. Click the button to browse for a *.csv file to load it into the datagrid.
Replace all the automatically generated code in your project with the code below.
This isn't my code, so all credit goes to the original author whose name I can't recall.

Code:

Public Class Form1

  Private FileName As String
  Private ds As New DataSet()

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

      'Configure the openfile dialog
      OpenFileDialog1.Filter = "csv files|; *.csv"
      OpenFileDialog1.Title = "Select a csv file"
      OpenFileDialog1.FileName = ""

      Try
          With OpenFileDialog1
              If .ShowDialog() = DialogResult.OK Then
                  FileName = .FileName
                  'Set chosen filename as contents of the RichTextBox
                  RichTextBox1.Text = FileName

                  Dim myData4 As DataTable = BuildDataTable(FileName, ";") 'Specify the delimiting character. I work with semicolon delimited files.
                  ds = New DataSet()
                  ds.Tables.Add(myData4)
                  DataGridView1.DataSource = myData4

                  'Count rows loaded into the datagrid. Useful for looping through the rows
                  Dim totalrows As Integer = DataGridView1.RowCount - 1
                  MessageBox.Show(totalrows & " rows were loaded into the datagrid!", "www.interloper.nl", MessageBoxButtons.OK, MessageBoxIcon.Information)

              End If
          End With

      Catch
      End Try

  End Sub

  'FUNCTION: Load csv into datagrid
  Private Function BuildDataTable(ByVal fileFullPath As String, ByVal separator As Char) As DataTable
      Dim myTable As DataTable = New DataTable("MyTable")
      Dim i As Integer
      Dim myRow As DataRow
      Dim fieldValues As String()
      Dim f As IO.File = Nothing
      Dim myReader As New IO.StreamReader(fileFullPath, System.Text.Encoding.UTF8)    'You might to change the encoding type

      Try
          fieldValues = myReader.ReadLine().Split(separator)

          For i = 0 To fieldValues.Length() - 1
              myTable.Columns.Add(New DataColumn("Field" & i))    'Column headers get Field# as name
          Next

          myRow = myTable.NewRow
          For i = 0 To fieldValues.Length() - 1
              myRow.Item(i) = fieldValues(i).ToString
          Next
          myTable.Rows.Add(myRow)
          While myReader.Peek() <> -1
              fieldValues = myReader.ReadLine().Split(separator)
              myRow = myTable.NewRow
              For i = 0 To fieldValues.Length() - 1
                  myRow.Item(i) = fieldValues(i).ToString
              Next
              myTable.Rows.Add(myRow)
          End While
      Catch ex As Exception
          MsgBox("Error building datatable: " & ex.Message)
          Return New DataTable("Empty")
      Finally
          myReader.Close()
      End Try

      Return myTable


  End Function
End Class



-------------------------------------------------------
Small Short Code

Dim myArray() As String = "asma , tehmina , sumera , sana , sohail"myArray = Split(line, ",")

Dim i As Integer
dim val as
For i = 0 To myArray.Length - 1
   val = myArray(i)                'read the value of the items in the array
   dg.Item(row, column) = val      'place it in the datagrid
Next

No comments:

Post a Comment