none
Error:syntax error in FROM clause(Microsoft jet databse engine)

    Question

  • I am trying import data from excel sheet into the sql server table.I amgetting the error message : Error:syntax error in FROM clause(Microsoft jet databse engine).

    Query to migrate the data:

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=E:\shared\Macro\AAAAAA.xls').

    Thanks,

    Ron.

    Tuesday, February 14, 2012 6:31 PM

Answers

  • 1) Because you are using the 'Microsoft.ACE.OLEDB.12.0 Provider, Install Microsoft Access Database Engine 2010 Redistributable if you haven't already done so.
       http://www.microsoft.com/download/en/details.aspx?id=13255 
        If you have SQL Server 64 bit then download 64bit version else 32bit version.

    If you install incorrect version you will get the following error: 
    Msg 7302, Level 16, State 1, Line 1
    Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    You can not install both versions.

    2) 
    use master 
    go 
    exec sp_configure 'Ad Hoc Distributed Queries',1
    RECONFIGURE WITH OVERRIDE
    go 


    3) use OPENROWSET by also supplying the query to perform within the excel. Each Excel sheet is treated as a table, whose table name = sheetname followed by $. 
    If first row in excel contains name of columns then use HDR=YES

    SELECT * FROM OPENROWSET (      
      'Microsoft.ACE.OLEDB.12.0',
      'Excel 12.0;Database=Database=E:\shared\Macro\AAAAAA.xls;HDR=YES', 
      'SELECT * FROM [Sheet1$]'
    )


    MCTS, MCITP - Please mark posts as answered where appropriate.



    • Proposed as answer by Jameel Ahmed's Tuesday, February 14, 2012 11:07 PM
    • Edited by Jameel Ahmed's Tuesday, February 14, 2012 11:09 PM
    • Marked as answer by Peja Tao Tuesday, February 21, 2012 7:00 AM
    Tuesday, February 14, 2012 11:06 PM