vb.net import csv file to devexpress gridview

Question

Currently Im facing problem in importing csv file to devexpress gridview,When i execute the code, the following error showed 'C:\New folder\QtimeAutomotiveByLot_new.csv' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. and the filepath works perfectly fine on my side. my code is as follow, can anyone guide me on this?

asp.net

<dx:ASPxGridView ID="DetailGridx" runat="server"  OnDataBinding="DetailGridx_DataBinding">

vb.net

  Protected Sub DetailGridx_DataBinding(sender As Object, e As EventArgs)

        Dim dt1 As New DataTable()
        Dim csvFileFolder As String = "C:\New folder\QtimeAutomotiveByLot_New.csv"
        'Dim csvFile As String = "QtimeAutomotiveByLot_New.csv"
        Dim strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + csvFileFolder + ";Extended Properties='Text;HDR=YES;IMEX=1;';"
        Dim connx As New OleDbConnection(strCon)
        Dim adapter1 As New OleDbDataAdapter
        connx.Open()

        Dim sql As New OleDbCommand("Select * FROM [" + csvFileFolder + "]", connx)

        adapter1.SelectCommand = sql

        adapter1.Fill(dt1)

        connx.Close()

        Dim detailGrid As ASPxGridView = CType(sender, ASPxGridView)
        Dim RowLotID As String = TryCast(sender, ASPxGridView).GetMasterRowFieldValues("LOTID")
        Dim ddata As DataView = New DataView(dt1)
        ddata.RowFilter = "LOTID = '" + RowLotID + "'"
        detailGrid.DataSource = ddata
        detailGrid.DataBind()

    End Sub

You are supposed to use the folder path in the connection string and the file name in the query, not the file path in both. Effectively, the folder is the database and the file is the table. — jmcilhinney, Aug 21 '18 at 09:12

score 1 · Accepted Answer · answered Aug 21 '18 at 09:14

Sounds like your current connection string to read CSV file is wrong. Unlike reading Excel files, when reading CSV files we're not specifying actual file name, but directory path where it belongs (see this issue).

The following example shows how to read CSV file using Jet 4.0 provider. Note that instead of setting IMEX=1, use FMT=Delimited property because IMEX primarily used for XLS and XLSX file format:

Protected Sub DetailGridx_DataBinding(sender As Object, e As EventArgs)

    Dim dt1 As New DataTable()
    Dim csvFileFolder As String = "C:\New folder\"
    Dim csvFile As String = "QtimeAutomotiveByLot_New.csv"

    ' specify directory path containing CSV file as data source
    Dim strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + csvFileFolder + ";Extended Properties='Text;HDR=YES;FMT=Delimited';"

    Dim connx As New OleDbConnection(strCon)
    Dim adapter1 As New OleDbDataAdapter
    connx.Open()

    ' specify actual file name here
    Dim sql As New OleDbCommand("Select * FROM [" + csvFile + "]", connx)

    adapter1.SelectCommand = sql

    adapter1.Fill(dt1)

    connx.Close()

    Dim detailGrid As ASPxGridView = CType(sender, ASPxGridView)
    Dim RowLotID As String = TryCast(sender, ASPxGridView).GetMasterRowFieldValues("LOTID")
    Dim ddata As DataView = New DataView(dt1)
    ddata.RowFilter = "LOTID = '" + RowLotID + "'"
    detailGrid.DataSource = ddata
    detailGrid.DataBind()

End Sub

vb.net import csv file to devexpress gridview

1 Answers1