ASP批量導入Excel到或者Access,Sql Server庫中
...
最近在研究批量導入數據的問題，這個其實沒有什么難點，只是如果一次性數據全部導入的話，要是數據量小的話，還可以，要是幾萬條，肯定會出現延時問題。我研究了一下，結合一個朋友的建議，寫了一個小導入程序，供大家參考！
<!--#Include File="Conn.asp"-->
<%
Dim ResultNum ’定義每次插入記錄數
Dim Page ’定義循環次數
ResultNum = 100 ’定義每次插入200條記錄
Page = Trim(Request.QueryString("Page"))
If Page = "" Then
Page = 1
End If
Page = Cint(Page)
PageTmp = Page-1 ’計算當前記錄數用
Set Rs = Server.CreateObject("Adodb.RecordSet")
Sql = "Select Count(ID) As Cnt From [Sheet1$]"
Rs.Open Sql,Conn,1,1
Cnt = Rs("Cnt")
Rs.Close
PageSize = Cnt/ResultNum+1 ’統計總頁數
If Page > PageSize Then
Set Rs = Nothing
Conn.Close
Set Conn = Nothing
Response.Write("已經到記錄末尾！")
Response.End()
End If
Sql = "Select * From [Sheet1$] Where Id Between "&(PageTmp*ResultNum+1)&" And "&Page*ResultNum&""
Rs.Open Sql,Conn,1,1
If Rs.Eof And Rs.Bof Then
%>
<table width="500" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td height="30" align="center">暫無商品!</td>
</tr>
</table>
<%
Else
%>
<table width="500" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td height="30" align="center">正在插入<%=(PageTmp*ResultNum+1)%>條到<%=Page*ResultNum%>條數據，請稍后……</td>
</tr>
</table>
<%
Connstr1 = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Extended properties=Excel 5.0;Data Source="& Server.MapPath("db/pdlfood.xls")
Set Conn1 = Server.CreateObject("Adodb.Connection")
Conn1.Open Connstr1
Do While Not Rs.Eof
Set Rs1 = Server.CreateObject("Adodb.RecordSet")
Sql1 = "Select * From [Sheet1$] Where FoodCode = "&Rs("FoodCode")&" "
Rs1.Open Sql1,Conn1,1,3
If Rs1.Eof And Rs1.Bof Then
Rs1.AddNew
Rs1("FoodCode") = Rs("FoodCode")
Rs1("FoodName") = Rs("FoodName")
Rs1.Update
Else
’不做處理
End If
Rs1.Close
Set Rs1 = Nothing
Rs.MoveNext
Loop
Conn1.Close
Set Conn1 = Nothing
End If
Rs.Close
Set Rs = Nothing
Conn.Close
Set Conn = Nothing
Response.Write("<script language=’javascript’>location.href(’?Page="&(Page+1)&"’);</script>")
%>
上一條：asp實現將excel中的數據批量導入到access
下一條：ASP字符串函數大全