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

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 .

Answer 1

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.

Answer 2

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()
adapter.Fill(ds)
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