CODEWITHSUNDEEP
c#exceloledb
user1294617
user1294617

Reputation: 43

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

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?

Upvotes: 3

Views: 10143

Answers (1)

GeorgeW
GeorgeW

Reputation: 11

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.

Upvotes: 1

Related Questions