ASP批量導(dǎo)入Excel到或者Access,Sql Server庫中

發(fā)布者：象牙塔編輯部發(fā)布時間：2009-12-04 07:50:27.000

...

最近在研究批量導(dǎo)入數(shù)據(jù)的問題，這個其實沒有什么難點，只是如果一次性數(shù)據(jù)全部導(dǎo)入的話，要是數(shù)據(jù)量小的話，還可以，要是幾萬條，肯定會出現(xiàn)延時問題。我研究了一下，結(jié)合一個朋友的建議，寫了一個小導(dǎo)入程序，供大家參考！


<%
Dim ResultNum ’定義每次插入記錄數(shù)
Dim Page ’定義循環(huán)次數(shù)
ResultNum = 100   ’定義每次插入200條記錄

Page = Trim(Request.QueryString("Page"))
If Page = "" Then
   Page = 1
End If
Page = Cint(Page)
PageTmp = Page-1 ’計算當(dāng)前記錄數(shù)用

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 ’統(tǒng)計總頁數(shù)
If Page > PageSize Then
   Set Rs = Nothing
   Conn.Close
   Set Conn = Nothing
   Response.Write("已經(jīng)到記錄末尾！")
   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%>條數(shù)據(jù)，請稍后……</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實現(xiàn)將excel中的數(shù)據(jù)批量導(dǎo)入到access

下一條：ASP字符串函數(shù)大全