How to get Column names from Excel using OLEDB query if the column name doesnot start from first row

Question

I am facing 2 issues in OLEDB query

(Problem 1)

I need to get the column names of user uploaded excel sheet. My connection string for excel is

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+fileNameAndPath+";
Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";

Then I will use this command

connection.Open();

DataTable schemaTable = connection.GetSchema("Columns");
Taking column name from each datarow like this
string name = (string)row["COLUMN_NAME"];

It is working fine, if the column name is present in first row of excel. But it will give the column name as "F1,F2,....." if the column name row doesn't start from first row. How can I solve this? is there any way to tell in query, from which row to take the column names?

(Problem 2)

Like the column header I need to select data from a particular range from excel. say like take data from row 3 to row 12. So I used the query like this,

Select RepName, Country from [Sheet1$3:12].

But it is throwing the exception,message like this "No value given for one or more required parameters."

Is any syntax error in my query?

if so can any one please tell how to correct the query?

I think problem 2 may be a consequence of problem 1. Unfortunately this method of talking to a spreadsheet will only use the first row of what it thinks is the data range to make columns name. If you have any control over the format of the user uploaded spreadsheet, I would move the column headers to row 1. If not you have to refer to your column names as F1, F2 instead of Repname and Country — Pynner
hi Pynner, the problem 2 is not a consequence of problem 1. i solved the problem 2 by giving the range in select statement, but i am not able to solve the problem1 — user1294617

GeorgeW GeorgeW · Accepted Answer · 2012-08-09T09:44:08

Problem 1 can not be solved through OLEDB, Excel is not a database and the only thing it can do is use the first row as column names or if the first row is blank, F1 etc.

The only way you can fix this is to use interop to either delete the blank rows and then query via OLEDB or parse the whole sheet through interop.

How to get Column names from Excel using OLEDB query if the column name doesnot start from first row

1 Answers