Welcome to Burgess's blog

本博除非注明,均是原创,您若转载,请指明出处,谢谢配合!

博客园 首页 新随笔 联系 订阅 管理

Here,I introduce some methods to import data from excel to DB,

1  By OPENDATASOURCE

SELECT
* FROM OPENDATASOURCE(
    'Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;DataBase=D:\TEST.xls')...[sheet1$]

Note: Sometimes, error message will occor when executing above script like this:
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.

The solution issetting Ad Hoc Distributed Queries to 1,you can refer to my post: http://www.cnblogs.com/Burgess/archive/2008/09/24/1298195.html


2   By Linked server

EXEC sp_addlinkedserver  --add linked server
    @server = N'MyExcel',
    @srvproduct = N'Jet 4.0',
    @provider = N'Microsoft.Jet.OLEDB.4.0',
    @datasrc = N'd:\TEST.xls',
    @provstr = N'Excel 8.0'
GO

Note:You can also add linked server by below method:

exec sp_addlinkedsrvlogin 'MyExcel','false' --login without account (Optional)
go

select * from MyExcel...sheet1$  --query data
go

 

 3  By VBA

 

Code


 4   By SQL Server Import and Export Wizard
Detailed oprating steps is abbreviated here.

5  Other methods:
Please refer to http://support.microsoft.com/default.aspx/kb/321686

 

posted on 2008-09-24 17:26  Burgess  阅读(2811)  评论(2编辑  收藏  举报