To import data from excel to DB

程序员文章站 2024-02-15 10:33:58

...

Here,I introducesome 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 exec

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:

To import data from excel to DB

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

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

3 By VBA

To import data from excel to DB Code
1Private Sub cmdInsert_Click()Sub cmdInsert_Click()
2
3Dim LinCnt As Integer
4 LinCnt = 6
5If InputBox("Please input password1", "口令输入框", , 8700, 4700) = "password" Then
6 Rows("6:6").Select
7 Rows("6:65536").Select
8 Range("A6").Select
9 To import data from excel to DB
10 Connection.Open " Provider=SQLOLEDB.1;Persist Security Info=True;User ID=User_id;Password=PWD; Initial Catalog=DB_name;Data Source=Server_ip;Connect Timeout=60 "
11 Connection.CursorLocation = adUseClient
12 CMD.ActiveConnection = Connection
13 To import data from excel to DB
14Do While Cells(LinCnt, "C") ""
15If txtSoldto.Text = "" And txtQcimat.Text = "" And Cells(LinCnt, "G") = "" Then
16MsgBox "Please maintain "
17Exit Sub
18Else
19 SQLstmt = " insert into table_name values('" & VBA.Trim(txt1.Text) & "','" & VBA.Trim(txt2.Text) & "','" & Cells(LinCnt, "C") & "','" & Cells(LinCnt, "D") & "','" & Cells(LinCnt, "E") & "','" & Cells(LinCnt, "F") & "','" & Cells(LinCnt, "G") & "','" & Cells(LinCnt, "H") & "','" & Cells(LinCnt, "I") & "','" & Cells(LinCnt, "J") & "') "
20 To import data from excel to DB CMD.CommandText = SQLstmt
21 CMD.Execute
22 LinCnt = LinCnt + 1
23End If
24Loop
25 Connection.Close
26Exit Sub
27Else
28MsgBox "You have no right to insert!"
29Exit Sub
30End If
31End Sub