Get data from excel into datatables with column names from different row RRS feed

  • Question

  • User136609187 posted

    I have an excel sheet in below format.While populating this in to datatable i want to take column names from the second row.

    Product Details and Location Details are merged columns that comes as first row and all column the names starts from second row

    Am using oledb connection to open excel and fill data table .

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Files\Productdetails.xlsx;Extended Properties="Excel 12.0;";

    Tried using HDR=No in the above connection string but it didnt help.

     Below is my sample excel data

    Product  Details                   Location Details
    ProductID Product Name Product Category Brand Country State City Location
    P221 D Link-DSL 2 Router Dlink India Maharashtra Mumbai Andheri
    PB3419 D Link-DIR Wifi Router Dlink India Maharashtra Mumbai Bandra

    Please help me to resolve this .

    Wednesday, March 23, 2016 5:40 AM

All replies

  • User269602965 posted

    Do you need to keep the first row?

    I get data like that to load often

    and I issue an OLEDB command to delete the first row

    then continue with my regular load now that the header is now ROW 1

    But I am tossing the EXCEL sheet as once loaded I do not need it any more.

    Wednesday, March 23, 2016 11:42 PM
  • User269602965 posted

    Alternatively you can skip one or more rows by copy table with skip rows before binding to your data grid

    Option Infer On
    Dim connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=Excel 12.0;", openFileDialog1.FileName)
    Dim intNumRowsSkip As Integer = 1
    Dim query As String = String.Format("select * from [{0}$]", "{YourSheeName}")
    Dim adapter = New OleDbDataAdapter(query, connectionString)
    Dim ds As New DataSet()
    Dim dt As DataTable = ds.Tables(0)
    Dim copyRows As IEnumerable(Of DataRow) = dt.AsEnumerable().Skip(intNumRowsSkip)
    Dim dt2 As DataTable = copyRows.CopyToDataTable()
    {YourGridName}.DataSource = dt2

    Friday, March 25, 2016 1:48 AM