如何用asp将csv数据导入到Access1
发布网友
发布时间：2023-10-28 20:05
共2个回答
热心网友
时间：2023-11-25 11:32
<%
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
%>
热心网友
时间：2023-11-25 11:33
以下代码亲测成功，更改其中数据库及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>")
%>
