将Excel数据导入到SQL Server数据库中（收集2）

Import/Export Excel (.Xlsx) or (.Xls) File into SQL Server
Filed in SQL Server 8 comments

To, Import/Export data To/From Excel (.xls) need Microsoft.Jet.OLEDB.4.0 and
for Excel 2007 (.xlsx) need 2007 Office System Driver: Data Connectivity Components.
You can download from here.http://www.microsoft.com/downloads/zh-cn/confirmation.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=zh-cn

Export Data to Excel file
Create an Excel file named testing having the headers same as that of SQLServerTable columns
Here is Query:
Excel 2003 (.Xls) file:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:testing.xls;', 'SELECT * FROM [Sheet1$]') select * from SQLServerTable

Excel 2007 (.Xlsx) file:

insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:testing.xlsx;', 'SELECT * FROM [Sheet1$]') select * from SQLServerTable

(dxj143)测试成功eg:

insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; HDR=Yes;DATABASE=d:\\test.xlsx', 'SELECT a1,a2,a3,a4 FROM [Sheet1$]')
select b1,b2,b3,b4 from testTable

注：需要在建好的excel里设置表头然后插入时要于sql表中的字段相对应才能导出excel成功

Import data from Excel to new SQL Server table
Excel 2003 (.Xls) file:

select * into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')
Excel 2007 (.Xlsx) file:

Select * into SQLServerTable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:testing.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')
Import data from Excel to existing SQL Server table
Excel 2003 (.Xls) file:

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')
Excel 2007 (.Xlsx) file:

INSERT INTO SQLServerTable select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:testing.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')
Import data from Excel to new SQL Server table with dynamic table name (ex. SQLTABLE_200110413)

DECLARE @table varchar(500)
DECLARE @Q varchar(2000 )
SET @table='SQLTABLE_' +(CONVERT(VARCHAR(8),GETDATE(),112))
SET @Q= 'select * into '+ @table + ' FROM OPENROWSET("Microsoft.ACE.OLEDB.12.0", "Excel 12.0;Database=D:testing.xlsx;HDR=YES", "SELECT * FROM [Sheet1$]")'
Exec(@Q)
May be you find error like below while Import/Export data To/From Excel
Error 1:

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)".
Here is solution might work for you..

USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
Error 2:

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.
here is solution might work for you

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
If above Query showing following error…

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.
then user RECONFIGURE WITH OVERRIDE instead of RECONFIGURE..

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE WITH OVERRIDE
If above solution is not working, ere is solution link might work for you..
http://www.kodyaz.com/articles/enable-Ad-Hoc-Distributed-Queries.aspx

Thanks.

Incoming search terms:
download microsoft ace oledb 12 0 excel (21)
OPENROWSET xlsx (15)
export data to xlsx from sqlserver (10)
Ashishblog com (6)
save excel data in sql server using jquery asp net (2)
gridview export to excel xlsx (2)
msg 15281 level 16 state 1 line 1sql 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 (1)
openrowset query for xlsx (1)
uploading excel file into sql server using asp net and C# (1)
upload excel files and add data to sql server asp (1)