• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 739
  • Last Modified:

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

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
cmh_rwhelan
Asked:
cmh_rwhelan
  • 6
  • 2
1 Solution
 
dlmilleCommented:
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
 
dlmilleCommented:
I realize my post was in VBA, but I'm assuming the same principles apply to C#.Net.  Let me know.

Dave
0
 
cmh_rwhelanAuthor Commented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
cmh_rwhelanAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now