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

Posted on 2011-10-17
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

Question by:cmh_rwhelan
    LVL 41

    Expert Comment

    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.


    LVL 41

    Expert Comment

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


    Author Comment

    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.
    LVL 41

    Expert Comment

    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?  
    LVL 41

    Expert Comment

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

    Let me know.

    LVL 41

    Expert Comment

    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.
    LVL 41

    Accepted Solution

    Finally.  Here's some documentation that is what we need!

    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"
        Set rs = Nothing
        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.


    Author Closing Comment

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now