T-SQL statement:

INSERT INTO tblSample
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\Test.xlsx',
'SELECT * FROM [Sheet1$]');

another way:

INSERT INTO tblSample SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\Test.xlsx;Extended Properties=EXCEL 12.0')... ;

Considerations:

You have to have 'Ad Hoc Distributed Queries' enabled:

• sp_configure 'show advanced options', 1;
• GO
• RECONFIGURE;
• GO
• sp_configure 'Ad Hoc Distributed Queries', 1;
• GO
• RECONFIGURE;
• GO

OPENROWSET can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider.

You have to have Microsoft Access Database Engine 2010 components (drivers) installed For described configuration AccessDatabaseEnginex64.exe should be used.

Important - the driver version you are installing is 14, but in you connection string you should use 'Microsoft.ACE.OLEDB.12.0', as it is pointed in instruction on download page. But the same instruction tells you "If you are connecting to Microsoft Office Excel data, add “Excel 14.0” to the Extended Properties of the OLEDB connection string." and that doesn't looks like to be working properly - I still had to use Excel 12.0.

Note: This download will install OELDB drivers for both MS Excel and MS Access 2010.

Be careful with first row - by default it is considered holding column names and will not be imported.

iazakharov's reply from 03-21-2011 02:07 is spot on. (Again :-) )

So, all I can do is to post pretty much the same statement:

INSERT INTO TargetTable
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\temp\ExcelSourceFile.xlsx',
'SELECT * FROM [Sheet1$]');

I can confirm the weird "requirement" to use Excel 12.0 in the connection string even if version 14 has been installed...

Additionally to what he already posted one more issue needs to be considered: The Excel file must be closed, of course :-)) (Otherwise there will be an error Msg 7399, Level 16, and Msg 7303, Level 16)

spconfigure 'show advanced options', 1; GO RECONFIGURE; GO spconfigure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO

            INSERT INTO ExcelTable
            SELECT * 
            FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
            'Excel 12.0 Xml;HDR=YES;Database=C:\Users\indika.k\Desktop\xx.xlsx',
            'SELECT * FROM [ExcelTable$]')

I can't execute select statement because the error: "The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered." Thank you

Just saw the email. I think it's too late for the iPad :)

Here is what I did at my company. Something similar to the posts above:

...... SET @Provider = 'Microsoft.ACE.OLEDB.12.0'

SET @ExcelString = 'Excel 12.0;Database=' + @ReportFile + ';Extended Properties=Excel 12.0 XML; HDR=YES'

SET @sqlstmt = 'INSERT INTO TargetTable SELECT * FROM OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [SheetName$A1:M#]'') '

EXEC (@sqlstmt) .......

"HDR=YES" is for the first row having column names. "[Sheet$A1:M#]" specifies the range of the data in the Excel file.

But just be careful, if you do this like daily and importing many Excel files, the process may hang there after certain time period.

Here is my bad experience, I have to export daily reports (about 15 Excel files), weekly reports (about 10 Excel files), and monthly reports (about 5 Excel files). Three SQL Server Agent jobs are scheduled to handle these reports. We have just migrated SQL Server 2008 (64-bit) on a Windows Server 2003 (64-bit) machine. Every two weeks, especially when that day has more than one job, the exporting process hangs. It doesn't fail, I couldn't kill the process. The only thing I could do is to restart the SQL Server service.

It never happened when we were using 32-bit OS with "Jet 4.0 Engine" to handle Excel. So I am guessing this "Microsoft.ACE.OLEDB.12.0" has some serious issues. I have asked questions on MSDN, but nobody can give an answer.

SQLServer Quiz 2011 - Query out Data from Excel to 64-bit SQL Server table

Solution:

Unfortunately, since there is no 64 bit provider, wecannot create linked servers directly to these data sources through (64 bit) SQL Server. From any 64 bit process, these providers are not available. But there is a work around. My solution is the same solution proposed by iazakharov. OPENROWSSET() function is used to access any OLE DB data source. All we need is the right OLEDB driver. There are few issues in loading excel sheet data into SQL Server 2008 64-bit.

First one is, we should have Ad Distributed Queries Enabled, which is shown below:

sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE; 
GO 
sp_configure 'Ad Hoc Distributed Queries', 1; 
GO 
RECONFIGURE; 
GO

Second one is we have to have Access Database Engine 2010 drivers installed and this should be of 64 bit.

My example excel file is located in D:\School. The sheet containing the list of Customers called TestSheet.

T-SQL to insert data from excel file to a SQL Server table is:

INSERT INTO Customers
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=D:\School\TestSheet.xlsx;',
'SELECT * FROM [TestSheet$]');

Furthermore, the query assumes that the first row contains the header, since we set it HDR=YES.

Issues that I encountered in loading data from excel to SQL Server and vice versa:

First issue:

To resolve this issue, I enabled the Ad Distributed Queries using sp_configure advanced options. The OPENROWSET() function expects that the Ad Hoc Distributed Queries option is enabled on the server. When that’s not the case you’ll see the following message:

Msg 15281, Level 16, State 1, Line 1 SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see "Surface Area Configuration" in SQL Server Books Online.

Second issue:

The OPENROWSET() function uses OLE DB, so it needs a driver for your data source, in this case for Excel. If the right driver is not installed, you’ll see the following error (or similar, depends on the version used).

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)".

To solve the issue, install the right driver and try again using the above query.

Opendatasource function can also be used with extended properties.

Conclusion:

The OPENROWSET() can be a useful function, given the right circumstances. But in the wrong setting it can be quite cumbersome to get to work.

Thanks,

Abi Chapagai