We help IT Professionals succeed at work.

Select * from Excel Sheet except Row 1.

Medium Priority
Last Modified: 2013-12-14
I'm trying to display a excel sheet but i want to exclude Row 1 which is the title and carry on from row 2 which is the start of the info I want.

Currently what i wrote is rather static as you can see I grabbed cells from A2:O36 which is row 2 onwards.But what I want is a dynamic solution that can retrive data from cell 2 till the last entry.Can anyone help?
                string connString = "provider=Microsoft.Jet.OLEDB.4.0;data source=" + gotdirectory + @";Extended Properties=""Excel 8.0;HDR=YES;""";
                OleDbConnection oledbConn = new OleDbConnection(connString);
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [A2:O36]", oledbConn);
                OleDbDataAdapter oleda = new OleDbDataAdapter();
                oleda.SelectCommand = cmd;
                userTable = ds.Tables[0];
                excelGridView.DataSource = ds.Tables[0].DefaultView;
                excelGridView.ReadOnly = true;

Open in new window

Watch Question

How about selecting from the entire sheet but use a where condition to only select rows which have a value in?


umm sorry I'm very weak in my coding could you possibly show me an example of it?
SELECT * FROM [A2:O65536] Where ColumnName <> ''"
Don't know if that is exactly right but shows my general idea.  You may have to experiment with it.


Sorry if my question was confusing but I was trying more of avoiding the use of A2:O36
As the database might go into thousands of data and exceed what i set initally and other maybe varies from columns so instead od doing a A2:ZZ9999 I was hoping for a coding solution to programmatically detect the table with values.
Thats what my idea was - it would Select everything but the where condition would make it only select rows with data in.  Not sure if this will actually work for Excel but the general idea is sound.


ummm sorry to sound noob but what does this <> expression mean?


Is there a Way to clasify the column name dynamically as my application reads from different tables and their column names are different.
Yes certainly, you can do things like this:

string columnName;
columnName = "MyColumnName";
string sqlCommandText = "SELECT * FROM [A2:O65536] Where " + columnName  + " <> ''"
OleDbCommand cmd = new OleDbCommand(sqlCommandText, oledbConn);

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


I got an exception error while trying your code.
No value given for one or more required parameters...
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.