SQL Server (x64) read from excel (openrowset) on 2008, 2012, 2014, 2016

There are too many possibilities may cause errors.
Here I list a easy and must steps to follow, this should be the most possible clear guide,

  1. If you are in a Windows AD (Domain) network, the username you are using must have at least read/write permission in the following folder (on the SQL Server)
    My personal suggestion is to put your own account into the server’s local Administrators group to test.

    C:\Users\SQLAdmin\AppData\Local\Temp
    

    Without this step, you can run the script locally (on the server, open SSMS), but you will see error when you run remotely like below,

    OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Unspecified error”.

    Msg 7303, Level 16, State 1, Line 1

    Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

  2. Assume you are running in a x64 (64 bits) OS, you should not install any 32 bits Office products. If so, try to uninstall it (and no guaranty it will work).
    Then you will need to download and install AccessDatabaseEngine_x64.exeFor 32 bit SQL Server, download AccessDatabaseEngine.exe
  3. Make sure to run the following script everytime before you run openrowset / opendatasource
    EXEC sp_configure 'Show Advanced Options', 1
    RECONFIGURE
    GO
    
    EXEC sp_configure 'Ad Hoc Distributed Queries', 1
    RECONFIGURE
    GO
    
    EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
    GO
    
    EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
    GO
    
  4. You should run the SQL script in the following, note the single quote should ONLY be used when sheetname starts with numbers
    --For sheetname starts with numbers, single quote is required.
    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\test.xls', ['2016Q1$']) --Local Path Sample
    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=\\servername\D$\test.xls', ['2016Q1$']) --UNC Path Sample
    SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:\test.xls;Extended Properties=Excel 12.0')...['2016Q1$'] --OpenDataSource Sample
    
    --For sheetname start with characters, single quote must be removed
    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\test.xlsx', [Sheetname$]) --Local Path Sample
    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=\\servername\D$\test.xlsx', [Sheetname$]) --UNC Path Sample
    SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:\test.xlsx;Extended Properties=Excel 12.0')...[Sheetname$] --OpenDataSource Sample
    
    
  5. Make sure the file you are reading is closed. And no password protection.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s