locked
Query to access named range RRS feed

  • Question

  • Hi all,

    I need to create a query in sql from my reporting services application which picks up data from an excel sheet.

    I can do SELECT * FROM [Sheet1$]

    Which gets all the contents from the spreadsheet.

    I was wondering how/if I can access a named range within my excel sheet through the sql statement?


    Thanks


    Code is Poetry
    Monday, May 4, 2009 7:59 AM

Answers

  • If I understand you correctly... if you have a range named MYDATA in your excel sheet (that is set up to be B3:F24 for example), you can just reference it directly in your SELECT:

    SELECT * FROM MyData

    If you have a specific Sheet/Row/Column range you want to select from, you can do that like so:

    SELECT * FROM [Sheet1$A4:C7]



    --Brad
    Monday, May 4, 2009 4:37 PM

All replies


  • I was wondering how/if I can access a named range within my excel sheet through the sql statement?



    Code is Poetry

    This part is not exactly clear what you mean by that (access a named range ), but basically you can query Excel by creating Linked server with Excel

    see the KB
    http://support.microsoft.com/default.aspx/kb/306397
    Mangal Pardeshi
    SQL With Mangal
    Monday, May 4, 2009 9:23 AM
  • I guess the result is returned as starting from F1 for column 1 in excel & so on.
    So, say, for accessing the top 10 values in columns 3 & 4

    SELECT top 10 F3, F4 From [Sheet1$].

    You can extend this by building a dynamic query string to be passed for specifying a range
    Monday, May 4, 2009 9:38 AM
  • If I understand you correctly... if you have a range named MYDATA in your excel sheet (that is set up to be B3:F24 for example), you can just reference it directly in your SELECT:

    SELECT * FROM MyData

    If you have a specific Sheet/Row/Column range you want to select from, you can do that like so:

    SELECT * FROM [Sheet1$A4:C7]



    --Brad
    Monday, May 4, 2009 4:37 PM
  • Hi Viral,

    It seems we cann't pass a named range into the statements.

    As Brad mentioned, we can use the range such as A1:B10.

    I suggest you convert the named range to cell range in front application, and then pass to the statement.

    Thanks,
    Jin
    Jin Chen - MSFT
    Wednesday, May 13, 2009 9:31 AM
  • Hi Viral,

    Sorry for the incorrect previous post.

    We can access named range by using the following statement:
    SELECT *
    FROM
    OPENROWSET('Microsoft.Jet.OLEDB.4.0'
    ,'Excel 8.0;Database=C:\test.xls'
    ,'SELECT * FROM [named]'
    )


    The "named" is the name of a range in Excel.

    Kind regards,
    Jin
    Jin Chen - MSFT
    Wednesday, June 3, 2009 7:36 AM