Post reply

How do I use SQL Query on Excel Data?

  • March 2, 2006 at 8:10 am

    #103107

    Hi,

    I want to create a DSN for an Excel workbook that has two worksheets. Then I want to do an outer join on the two worksheets to compare data. What software tool can I use and how do I do this?

    Example

    select * from <dsn>.<sheet1> left outer join <dsn>.<sheet2> on sheet1.column1 = sheet2.column1

    Thanks 

     

     

  • March 3, 2006 at 7:10 am

    #624596

    You can define a linked server to that excel file, and T-SQL SELECT statement to do the join etc.

     

  • March 3, 2006 at 7:51 am

    #624616

    I will generally use the Linked Server method for data links that tend to be more permanent.  For the one time scenarios I usually use MS Access and either link to or import the Excel data.  If I need it in a SQL DB for some reason, I will use a DTS import.

  • March 3, 2006 at 9:45 am

    #624654

    You can use OPENDATASOURCE in a query e.g

    SELECT *

    FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="C:\TestJoins.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$ AS s1

    INNER JOIN OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="C:\TestJoins.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet2$ AS s2 ON s2.KeyID=s1.KeyID

    Where the first row of each Excel Sheet contains the column names (e.g. KeyID)

    Hope this helps

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply