使用 Microsoft.Jet.OLEDB.4.0 Provider 開啟 Excel 檔案(Extended Properties=Excel 8.0),以 SQL 查詢工作表資料,逐筆 INSERT 至目標資料庫。
<!--#include file="adoconn.asp"-->
<%
Set xlsConn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
source1 = Server.MapPath("..") & "\" & Replace(Request("path"), "/", "\")
myConn_Xls = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & source1 & ";Extended Properties=Excel 8.0"
xlsConn.Open myConn_Xls
sql = "SELECT * FROM [商品列表$]"
Set rs = xlsConn.Execute(sql)
If Not rs.EOF Then
While Not rs.EOF
names = rs("商品名")
pic = rs("圖片")
sql = "INSERT INTO [product] (namese, pic) VALUES ('" & names & "','" & pic & "')"
conn.Execute(sql)
rs.MoveNext
Wend
Response.Write "匯入成功"
End If
%>
<%
Set conn = Server.CreateObject("ADODB.Connection")
db = "../database/db11.mdb"
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(db)
conn.Open connStr
%>
Replace(name, "'", "''")。Excel 8.0 僅支援 .xls(Excel 97-2003)。.xlsx 需用 Microsoft.ACE.OLEDB.12.0 + Extended Properties="Excel 12.0 Xml;HDR=YES"。Execute 極慢,大量資料應用 ADODB.Recordset.AddNew/Update 批次寫入,或使用 BULK INSERT(SQL Server)、LOAD DATA INFILE(MySQL)。On Error Resume Next 與交易控制(BeginTrans/CommitTrans/RollbackTrans)。