We help IT Professionals succeed at work.

Select * from Excel Sheet except Row 1.

csharp_learner
on
Medium Priority
2,159 Views
Last Modified: 2013-12-14
Hi,
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?
try
            {
                string connString = "provider=Microsoft.Jet.OLEDB.4.0;data source=" + gotdirectory + @";Extended Properties=""Excel 8.0;HDR=YES;""";
                OleDbConnection oledbConn = new OleDbConnection(connString);
                oledbConn.Open();
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [A2:O36]", oledbConn);
                OleDbDataAdapter oleda = new OleDbDataAdapter();
                oleda.SelectCommand = cmd;
                oleda.Fill(ds);
                userTable = ds.Tables[0];
                excelGridView.DataSource = ds.Tables[0].DefaultView;
                excelGridView.ReadOnly = true;
                oledbConn.Close();
            }

Open in new window

Comment
Watch Question

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

Author

Commented:
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.

Author

Commented:
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.

Author

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

Author

Commented:
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

Author

Commented:
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.

OR

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.