?
Solved

using OLEDB to connect to Excel - how to query starting at row 4 when count of rows is unknown

Posted on 2011-10-17
8
Medium Priority
?
628 Views
Last Modified: 2013-12-16
I want to use an OleDbDataAdapter to query an Excel worksheet.  I have the OleDbConnection object working fine, and I can connect to the Excel sheet with a simple SELECT statement.

However, all the sheets I want to connect to have the headers on row 4, not on row 1.  So I want to SELECT all the rows that have data, starting at row 4.

Something like:  "SELECT * FROM [Device$A4:*]";

If i could fugure out how to count the rows in the worksheet, then I could define a range to query .. or maybe there is a better way.

Any ideas??
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
    @"Data Source=" + file +
    ";Extended Properties=" + Convert.ToChar(34).ToString() +
    "Excel 8.0;HDR=YES" + Convert.ToChar(34).ToString();
string cmdText = "SELECT * FROM [Devices$]";
OleDbConnection connect = new OleDbConnection(connString);
OleDbCommand cmd = new OleDbCommand(cmdText, connect);

Open in new window

0
Comment
Question by:cmh_rwhelan
  • 6
  • 2
8 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 36984200
First, in the workbook you're querying, you could define a range name that you could then query against in your macro.  Then, you could set

rngAddress = "myRangeName"
cmdText = "SELECT * from [" & rngAddress & "]"

However, looking at your request more closely, I must say that there's no need to determine the number of rows in the worksheet - the query will get all rows, and stop when it hits null rows.

For Example, Select * from [Sheet1$] - should load the entire dataset into the result set.  In the attached, the first query does this, loading columns A and B into the recordset, with the data starting on Row 4, going to Row 24.  As a result of the query, records from Row 4 thru Row 24 are available.  In the attached, when you run the macro, it will tell you there are 19 rows of data.

See attached.  Upload a mock example, if you're not experiencing this with your example.

Cheers,

Dave
simpleSQLWkb.xls
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36984207
I realize my post was in VBA, but I'm assuming the same principles apply to C#.Net.  Let me know.

Dave
0
 

Author Comment

by:cmh_rwhelan
ID: 36989527
You solution certainly is the best one if the first 4 rows have null values, but in my case the first 3 rows contain titles, date and other stuff I'm not interested in, then the header row for the data is in row 4 then the nex X rows are the data.  So I need to query a range starting at row 4 ending with the first null row...

See the file I am trying to query for reference.
DeviceList.xls
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 42

Expert Comment

by:dlmille
ID: 36990072
Is the workbook closed that you're querying against, or is it open - is it the same workbook with the code or separate?  Can we do some VBA against the closed file to get the range, if its closed?  
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36990076
If the workbook is open, its simple VBA to get the rowcount.  If closed, also do-able with some arm twisting.

Let me know.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36990157
Well, I feel like a hacker, lol.  Why aren't things documented well???? hmmm.

This works:

strSQL = " SELECT * FROM A4:IU" & Rows.Count  'Note: null columns/rows don't come back, so we don't need last row - we could use an earlier column and row max of 65535 for Excel 2003, if we're running from 2007+

Working on how to specify the sheet.
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 36990182
Finally.  Here's some documentation that is what we need!

http://www.fontstuff.com/ebooks/free/fsADOConnectExcel.pdf

Apparently, you were perhaps further along with this than I was.  At any rate, I modified my simple ADO sub to go against your Devices tab.

Here's the modified code.  Again, no need to "know" the number of rows or columns, as ADO will ignore null rows/columns - I would just ensure there aren't any gaps in the data that ADO can't process - e.g., a set of columns, then some null columns, then more, if you're querying the entire sheet for data.

Here's the modified code:
 
Public Sub LoadWithADODB()

    Dim cn As ADODB.Connection, rs As ADODB.Recordset, strSQL As String
    Dim a As Variant
    
    Set cn = New ADODB.Connection
    
    ' **** You need the commented connection string for Excel 2003 or earlier
    'cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes"";"
    
    Set rs = New ADODB.Recordset
    
    strSQL = " SELECT * FROM [Devices$A4:IU" & Rows.Count & "]"
    
    Debug.Print "Opening recordset"
    rs.Open strSQL, cn
    
    If Not rs.EOF Then
    Debug.Print "getting rows"
        a = rs.GetRows
    End If
    
    MsgBox "Your data has " & UBound(a, 2) & " Rows of data"
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Sub

Open in new window


After running it with debugger, I could see the variant "a" with the right number of rows/columns, so works like a charm!

See attached demo workbook.

Dave
simpleSQLWkb-r2.xls
0
 

Author Closing Comment

by:cmh_rwhelan
ID: 36990341
Seems like there should be a simpler way, but it does work well as you say.... I'll update this post if I come across a better solution, but so far so good.

Thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

840 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