Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    2

    Unanswered: SQL to lookup in Excel

    Hi,

    I'm whiting a SQL statement where I want to use to find records in a SQL table that also is listed in a Excel sheet.

    First I try looking som specific records up in my excel sheet bu I don't know how to use a where in the query.

    When I use this:

    select *
    FROM OpenRowSet('MSDASQL','Driver=Microsoft Excel Driver (*.xls); DBQ=c:\data\test.xls', 'SELECT * FROM [Sheet1$] ' )

    I get all records from the test.xls and that OK,
    but when I try to add a Where to just get one of the records like:

    select *
    FROM OpenRowSet('MSDASQL','Driver=Microsoft Excel Driver (*.xls); DBQ=c:\data\test.xls', 'SELECT * FROM [Sheet1$] WHERE DP_ID = '008000002' ' )

    I get this error message:
    Server: Msg 170, Level 15, State 1, Line 2
    Line 2: Incorrect syntax near '008000002'.

    Can any one help me?

    Best regards
    Jens

  2. #2
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42

    Re: SQL to lookup in Excel

    Hi there,

    Try this :

    select *
    FROM OpenRowSet('MSDASQL','Driver=Microsoft Excel Driver (*.xls); DBQ=c:\data\test.xls', 'SELECT * FROM [Sheet1$] WHERE DP_ID = ''008000002'' ' )

    Good luck!
    Shadow to Light

  3. #3
    Join Date
    May 2003
    Posts
    2

    Re: SQL to lookup in Excel

    Originally posted by Crespo-n00b
    Hi there,

    Try this :

    select *
    FROM OpenRowSet('MSDASQL','Driver=Microsoft Excel Driver (*.xls); DBQ=c:\data\test.xls', 'SELECT * FROM [Sheet1$] WHERE DP_ID = ''008000002'' ' )

    Good luck!
    Thanks but this gives me:
    Server: Msg 7320, Level 16, State 2, Line 1
    Could not execute query against OLE DB provider 'MSDASQL'.
    [OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] Too few parameters. Expected 2.]
    OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandText::Execute returned 0x80040e10].

    BR Jens

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •