[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Select * from Excel Sheet except Row 1.

Posted on 2009-02-18
10
Medium Priority
?
2,141 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

0
Comment
Question by:csharp_learner
  • 5
  • 5
10 Comments
 
LVL 19

Expert Comment

by:daveamour
ID: 23668834
How about selecting from the entire sheet but use a where condition to only select rows which have a value in?
0
 

Author Comment

by:csharp_learner
ID: 23668860
umm sorry I'm very weak in my coding could you possibly show me an example of it?
0
 
LVL 19

Expert Comment

by:daveamour
ID: 23668896
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:csharp_learner
ID: 23668961
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.
0
 
LVL 19

Expert Comment

by:daveamour
ID: 23669002
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.
0
 

Author Comment

by:csharp_learner
ID: 23677145
ummm sorry to sound noob but what does this <> expression mean?
0
 
LVL 19

Expert Comment

by:daveamour
ID: 23678287
0
 

Author Comment

by:csharp_learner
ID: 23688660
Is there a Way to clasify the column name dynamically as my application reads from different tables and their column names are different.
0
 
LVL 19

Accepted Solution

by:
daveamour earned 525 total points
ID: 23689276
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);
0
 

Author Comment

by:csharp_learner
ID: 23707676
I got an exception error while trying your code.
No value given for one or more required parameters...
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.
Suggested Courses
Course of the Month19 days, 7 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question