C# reading excel file where the header is not the first row

Question

I'm trying to read data from an excel file. Full code below. The first few lines are junk, so skip them using the following

"SELECT * From [" + SheetName + "] WHERE [F3] <> ''";

I want to read the code to a datatable and still keep the headers. which appear after the junk lines.

The problem is that when I filter our the junk lines using the WHERE clause above, the datatable column titles come out as F1,F2 etc

In my connection string I do specify I want the headers

HDR=Yes.

If I remove the WHERE clause from the SELECT, it works as I expect.

Please advise

switch (Extension)
            {
                case ".xls": //Excel 97-03
                    conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                    break;
                case ".xlsx": //Excel 07
                    conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                    break;
            }
            conStr = String.Format(conStr, FilePath, "Yes");
            OleDbConnection connExcel = new OleDbConnection(conStr);
            OleDbCommand cmdExcel = new OleDbCommand();
            OleDbDataAdapter oda = new OleDbDataAdapter();
            DataTable dt = new DataTable();
            cmdExcel.Connection = connExcel;

            //Get the name of First Sheet
            connExcel.Open();
            DataTable dtExcelSchema;
            dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string SheetName = ListSheets.SelectedValue;
            connExcel.Close();

            //Read Data from First Sheet
            connExcel.Open();
            cmdExcel.CommandText = "SELECT * From [" + SheetName + "] WHERE [F3] <> ''";
            oda.SelectCommand = cmdExcel;
            oda.Fill(dt);


     connExcel.Close();

Praveen Paulose · Accepted Answer · 2015-03-27 14:41:23Z

6

Assuming you have fixed lines of junk code in your Excel Sheet, you could execute the query like this.

"SELECT * From [Sheet1$A5:C]"

The assumption here is that your headers are on line 5 and C is the column where your data ends. This correctly loads the header names for the columns.

Adding a snapshot of the Excel against which the above query works.

enter image description here

edited Mar 27, 2015 at 14:41

answered Mar 25, 2015 at 22:00

Praveen Paulose

5,7711 gold badge19 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

totalitarian Over a year ago

Because that way the headers from excel don't get put into the data table as headers

Praveen Paulose Over a year ago

are the no. of junk lines fixed in your sheet?

Praveen Paulose Over a year ago

I have updated the answer. Please check if that works for you. I have run it locally and it seems to do what you ask for.

totalitarian Over a year ago

That seems to get the headers but not the data

Praveen Paulose Over a year ago

It does get the data too. What is the row count you get?

Collectives™ on Stack Overflow

C# reading excel file where the header is not the first row

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related