locked
Importing Excel spreadsheets to SQL 2008 table RRS feed

  • Question

  • I have over 40 excel 2007 spreadsheets to import into a SQL 2008 table. I have found several suggestions that look something like this:

    SELECT *
    INTO xxxx
    FROM
    OPENROWSET('Microsoft.ACE.OLEDB.12.0'
    ,'Excel 8.0;Database=D:\SharePointTempDocuments\ASA\CO_02_New_Chart_of_Accounts.xlsx'
    ,'SELECT * FROM [Posting$]'
    )

    I get the following error:

    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
    Msg 7350, Level 16, State 2, Line 1
    Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    I'm assuming that the reference to the null server results for the access denied error but I'm not sure. I am logged into the SQL server as a domain administrator and the SQL service is started by another domain administrator.

    This type of import will occur frequently in the future and I would really like to automate it. Any suggestions would be greatly appreciated.
    Sunday, January 13, 2013 6:31 PM

Answers

  • I've gotten that error a few times.  It usually means either the Excel file is open (me or another user), or that the account SQL is running under doesn't have permission for the file, or that the account I'm using doesn't have permission.

    I've not had luck with "read" permissions, I've usually had to have the file somewhere that I have full access.

    I've also had problems with network paths giving that error, but local paths not.  It doesn't recognize mapped drives, so far as I know, but I think that gives a different error.

    Of course, Excel files can also be password protected, so that could give the same error, but I'm assuming you've already checked that.


    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread
    • Marked as answer by PAPIAS53 Wednesday, January 16, 2013 3:55 PM
    Sunday, January 13, 2013 6:40 PM

All replies