如何用asp将csv数据导入到Access

Question

asp高手帮帮忙，如何用asp将csv或者excel数据导入到Access(每一次导入直接替换已有的数据）。最好是能让用户选择csv或者excel文件，求代码！~
我找到了以下代码 也看懂了 但总是显示“倒入失败”
<%
dim conn
dim conn2
On Error Resume Next
Server.ScriptTimeOut = 999999
set conn=CreateObject("ADODB.Connection")
conn.Open "Provider =Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Data Source="&Server.MapPath ("test.mdb")
set conn2=CreateObject("ADODB.Connection")
conn2.Open "Provider =Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Extended properties =Excel 5.0;Data Source="&Server.MapPath("test.xls") xls
sql = "SELECT * FROM [Sheet1$]"
set rs = conn2.execute(sql)
while not rs.eof
sql2 = "insert into tb_zhenya(zhenya) values('"& rs(0) &"')" conn.execute(sql2)
rs.movenext
Response.Write "正在插入 "&sql2&"<Br>"
Response.Flush
wend
conn.close
set conn = nothing
conn2.close
set conn2 = Nothing
If Err = 0 Then
Response.Write "导入成功"
Else
Response.Write "导入失败!"
End If
%>

Answer 1

<%Response.Buffer=True%>
<%
dim conn
dim connstr
dim db
db="excel.mdb" '数据库文件位置
on error resume next
connstr="DBQ="+server.mappath(""&db&"")+";DefaultDir=;DRIVER={Microsoft Access Driver (*.mdb)};"
set conn=server.createobject("ADODB.CONNECTION")
if err then
err.clear
else
conn.open connstr
end if

sub CloseConn()
conn.close
set conn=nothing
end sub
Server.ScriptTimeout=999
%>
<%
dim xxlsfile,lxlsfile,action,sfile,conn3,conn2
xxlsfile=request.Form("backfile")
lxlsfile=request.Form("backfile2")
action=request.Form("sj")

if action="xsj" then
sfile=Server.MapPath("../pic/excel/")&"\"&xxlsfile
else
sfile=Server.MapPath("UploadFiles2")&"\"&lxlsfile
end if
if sfile="" then
response.Write("出错了，请与程序设计人员联系！")
else

set conn2=CreateObject("ADODB.Connection")
conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Extended properties=Excel 5.0;Data Source="&sfile

if action="xsj" then
sql = "SELECT * FROM [Sheet1$]"
set rs = conn2.execute(sql)
while not rs.eof
'fkrq=left(rs(7),4)&"-"&mid(rs(7),5,2)&"-"&right(rs(7),2)
'jzrq=left(rs(8),4)&"-"&mid(rs(8),5,2)&"-"&right(rs(8),2)

sql1 = "insert into bid([时间],[省份],[通用名],[商品名],[剂型],[规格],[转换系数],[材质],[使用单位],[包装单位],[质量层次],[生产厂家],[投标企业],[中标价格],[零售价],[包装单位限价],[最小制剂报价],[入围情况]) values('"& fixsql(rs(0)) &"','"& fixsql(rs(1)) &"','"& fixsql(rs(2)) &"','"& fixsql(rs(3)) &"','"& fixsql(rs(4)) &"','"& fixsql(rs(5)) &"','"& fixsql(rs(6)) &"','"& fixsql(rs(7))&"','"& fixsql(rs(8)) &"','"& fixsql(rs(9)) &"','"& fixsql(rs(10)) &"','"& fixsql(rs(11)) &"','"& fixsql(rs(12)) &"','"& fixsql(rs(13)) &"','"& fixsql(rs(14)) &"','"& fixsql(rs(15)) &"','"& fixsql(rs(16)) &"','"& fixsql(rs(17)) &"')"

Conn.execute(sql1)

rs.movenext
wend
%>
<table width="193" align="center">
<tr>
<td width="144">新数据导入成功！</td>
<td width="37" height="20" ><button class="button" onClick="window.history.go(-1)">返回</button></td>

</tr>
</table>
<%
else
sqll = "SELECT * FROM [Sheet1$]"
set rsl = conn2.execute(sqll)
while not rsl.eof
fkrq=left(rsl(7),4)&"-"&mid(rsl(7),5,2)&"-"&right(rsl(7),2)
jzrq=left(rsl(8),4)&"-"&mid(rsl(8),5,2)&"-"&right(rsl(8),2)

sqll2 = "Update member Set [ljxfje]='"&fixsql(rsl(5))&"',[score]='"&fixsql(rsl(6))&"' where uid='"&fixsql(rsl(0))&"'"
Conn.execute(sqll2)

rsl.movenext
wend
%>
<table width="193" align="center">
<tr>
<td width="144">老数据更新成功！</td>
<td width="37" height="20" ><button class="button" onClick="window.history.go(-1)">返回</button></td>

</tr>
</table>

<%
end if
'Connoff
set conn = nothing
conn2.close
set conn2 = nothing

function fixsql(str)
dim newstr
newstr = str
if isnull(newstr) then
newstr = ""
else
newstr = replace(newstr,"'","''")
end if
fixsql = newstr
end function
end if
%>

Answer 2

先读取CSV文件，然后读取表中的数据将其写入到数据库中；
以下代码亲测成功，更改其中数据库及csv文件名即可；
pic.csv 为要导入的csv文件名
pic.mdb 为要导入的数据库名称
注意：数据库中的字段名要与表中的列名称一致

<%
'导入csv数据
i=0
Dim cn,oConn,connstr
'打csv.
Set cn = Server.CreateObject("ADODB.Connection")
cn.Provider = "Microsoft.Jet.OLEDB.4.0 "
cn.ConnectionString = "Data Source=" & Server.MapPath("pic.csv") & ";" & _
"Extended Properties=Excel 8.0;"
cn.Open
'打开MDB.
connstr="DBQ="+server.MapPath("pic.mdb")+";DefaultDir=;DRIVER={Microsoft Access Driver (*.mdb)};"
set oConn=server.CreateObject("ADODB.CONNECTION")
oConn.open connstr
'读取数据.
set rsRead=server.CreateObject("ADODB.Recordset")
rsRead.Open "select * from [pic$]",cn,1,1
do until rsRead.EOF
'写入数据库.
oConn.Execute("Insert into iepic(pic)Values('"& rsRead.Fields("pic") & "')" )
rsRead.MoveNext
i=i+1
loop
response.write("<p align=center>成功导入"&i&"条数据</p><br>")
response.Write("<p align=center><a href=javascript:window.close()>关闭窗口</a></p>")
%>