Data Table and Datagridview problem

[mishphar]

I have a csv file in drive C: when I am going to display in datagridview but the 1st line is not display in gatagridview, My csv file content data is as follows

"^NSEI",5040.00,"9/2/2011","6:00am",+39.00,0.00,0.00,0.00,0
"RCOM.NS",84.70,"9/2/2011","6:29am",+5.15,81.90,85.65,79.70,13378691
"RELIANCE.NS",805.45,"9/2/2011","6:29am",+22.85,796.70,813.70,789.45,7791545
"DLF.NS",207.85,"9/2/2011","6:28am",+11.10,194.25,209.90,191.05,10297314
"UNITECH.NS",26.85,"9/2/2011","6:30am",-0.75,27.80,28.45,26.70,44035656
"HANUNG.NS",101.10,"9/2/2011","6:29am",-0.50,102.35,104.80,100.60,326326
"LITL.NS",16.75,"9/2/2011","6:29am",-0.30,17.00,17.50,16.35,9415675
"INDIABULL.NS",149.95,"9/2/2011","6:28am",-4.00,155.00,157.00,148.50,677258

My code is below:

Dim csvFileFolder As String = "C:\"
        Dim csvFileName As String = "Test2.csv"

        
        Dim connString As String = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" _
            & csvFileFolder & ";Extended Properties=""Text;HDR=No;FMT=Delimited"""
        Dim conn As New Odbc.OdbcConnection(connString)

        'Open a data adapter, specifying the file name to load
        Dim da As New Odbc.OdbcDataAdapter("SELECT * FROM [" & csvFileName & "]", conn)
        'Then fill a data table, which can be bound to a grid
        Dim dt As New DataTable
        da.Fill(dt)
        'MsgBox(dt)


        DataGridView2.DataSource = dt

somebody please help me to resolve the problem

[GeekByChoiCe]

This is just a wild shot, so please don't nail me on it.
But could it be that the datagrid is expecting the column names in the first row?
So you could try to add in the very first line the column names and see what is going on.
OR
There is might be a extended option in the connection string where you can define if the first row are column names or not.

[Phasma]

Imports System.IO
Imports System.Data.OleDb
Public Class Form1

    '1 DataGridView - dgv_csv
    'See Attached jpg's

  Private Sub Form1_Load(ByVal sender As System.Object, _
                         ByVal e As System.EventArgs) Handles MyBase.Load

    Dim dt As New DataTable
    dt = CreateDataTableFrom_CSV("Sample.csv", _
                                 My.Computer.FileSystem.SpecialDirectories.Desktop)

    dgv_csv.DataSource = dt

  End Sub


  Private Function CreateDataTableFrom_CSV(ByVal csvFileName As String, _
                                           ByVal csvDir As String) As DataTable


    Dim strConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & csvDir & _
                                  ";Extended Properties=""Text;HDR=No;FMT=Delimited"""

    Dim conn As New OleDbConnection(strConnString)
    Try
      conn.Open()
      Dim cmd As New OleDbCommand("SELECT * FROM [" & csvFileName & "]", conn)
      Dim da As New OleDbDataAdapter()
      Dim ds As New DataSet

      da.SelectCommand = cmd
      da.Fill(ds)
      da.Dispose()

      'If you want to create Headers uncomment the following 
      'and comment out the return ds.Tables(0)
      'Or add the Headers to the csv file on the first line 
      'and change HDR=No to Yes in the connection string. 
      'Which way is easier is up to you.
      

      '****************************To Add Headers***************************
      'Dim dt As New DataTable
      'dt = CreateDataTable("Sample")


      'Dim i As Integer = 0
      'For Each rw As DataRow In ds.Tables(0).Rows
      '  dt.Rows.Add()
      '  dt.Rows(i).Item(0) = rw.Item(0)
      '  dt.Rows(i).Item(1) = rw.Item(1)
      '  dt.Rows(i).Item(2) = rw.Item(2)
      '  dt.Rows(i).Item(3) = rw.Item(3)
      '  dt.Rows(i).Item(4) = rw.Item(4)
      '  dt.Rows(i).Item(5) = rw.Item(5)
      '  dt.Rows(i).Item(6) = rw.Item(6)
      '  dt.Rows(i).Item(7) = rw.Item(7)
      '  dt.Rows(i).Item(8) = rw.Item(8)
      '  i += 1
      'Next

      'Return dt
      '***************************End Add Headers****************************


      Return ds.Tables(0)   'Comment Out For The Headers

    Catch ex As Exception
      MsgBox(ex.ToString)
      Return Nothing
    Finally
      conn.Close()
    End Try

  End Function


  Public Function CreateDataTable(ByVal tableName As String) As DataTable
    '"RELIANCE.NS",805.45,"9/2/2011","6:29am",+22.85,796.70,813.70,789.45,7791545

    Dim dt As New DataTable
    dt.Columns.Add("NAME", GetType(System.String))
    dt.Columns.Add("Value", GetType(System.Decimal))
    dt.Columns.Add("DATE", GetType(System.DateTime))
    dt.Columns.Add("Time", GetType(System.DateTime))
    dt.Columns.Add("Value2", GetType(System.Decimal))
    dt.Columns.Add("Value3", GetType(System.Decimal))
    dt.Columns.Add("Value4", GetType(System.Decimal))
    dt.Columns.Add("Value5", GetType(System.Decimal))
    dt.Columns.Add("Value6", GetType(System.Decimal))
    dt.TableName = tableName
    Return dt

  End Function


End Class