Home>Network programming tutorial>ASP tutorial

Asp implements data import in Excel into database

Author：Eve Cole Update Time：2025-03-17 07:00:03

This article summarizes the methods of using Asp to import data from Excel into the database. Friends who need it can refer to it.

Asp implements data import in Excel to database

  1. <%Response.CodePage=65001%>
  2. <%Response.Charset=UTF-8%>
  3. <%
  4. wenjian=request.Form(select)
  6. 'Get the file extension
  7. ext=FileExec(wenjian)
  8. 'Judge file extension
  9. ifext<>xlsthen
  10. response.Write(<script>alert('The file type is incorrect, please verify!');window.location.href='index.html';</script>)
  11. response.End()
  12. endif
  14. DimobjConn,objRS
  15. DimstrConn, strSql
  17. setobjConn=Server.CreateObject(ADODB.Connection)
  18. setobjRS=Server.CreateObject(ADODB.Recordset)
  20. excelFile=server.mappath(wenjian)
  21. 'For excel2007
  22. strConn=Provider=Microsoft.ACE.OLEDB.12.0;DataSource=&excelFile&;&ExtendedProperties=Excel8.0;
  23. objConn.OpenstrConn
  25. strSql=SELECT*FROM[Sheet1$]
  27. objRS.OpenstrSql,objConn,1,1
  28. objRS.MoveFirst
  30. %><!--#includefile=conn.asp--><%
  31. 'Loop all records in excel
  32. whilenotobjRS.eof
  34. setrs=Server.CreateObject(Adodb.Recordset)
  35. 'Query statement
  36. sql_s=select*fromceshiwhereelname='&objRS(0)&'andold='&objRS(1)&'andsex='&objRS(2)&'andguojia='&objRS(3)&'andQQ='&objRS(4)&''
  37. rs.opensql_s,conn,1,1
  38. 'Don't enter the duplicate data
  39. ifrs.eofthen
  40. 'Insert statement
  41. '****The first item in excel will not be entered****
  42. sql=insertintoceshi(lname,old,sex,guojia,QQ)values('&objRS(0)&','&objRS(1)&','&objRS(2)&','&objRS(3)&','&objRS(3)&','&objRS (4)&')
  43. 'Execute insertion
  44. conn.execute(sql)
  45. endif
  46. objRS.MoveNext
  47. rs.close
  48. setrs=nothing
  49. wend
  51. 'It's time to close again
  52. conn.close
  53. setconn=nothing
  54. objRS.Close
  55. objConn.Close
  56. setobjRS=Nothing
  57. setobjConn=Nothing
  59. response.Write(<script>alert('Import Successfully');window.location.href='index.html';</script>)
  60. response.End()
  62. FunctionFileExec(fileName)
  63. FileExec=Mid(fileName,Instr(fileName,.)+1,Len(fileName)-Instr(fileName,.))
  64. EndFunction
  65. %>

Share another simplified version of the code

  1. wenjian=request.Form(floor)
  2. fileext=mid(wenjian,InStrRev(wenjian,.)+1)
  3. iflcase(fileext)<>xlsthen
  4. response.write<script>alert('The file format is incorrect, please upload the Excel file');window.location.href='updateFloor.asp';</script>
  5. response.end
  6. endif
  7. setconne=server.CreateObject(ADODB.Connection)
  8. connStre=Provider=Microsoft.Jet.OLEDB.4.0;DataSource=&Server.MapPath(&wenjian&)&;ExtendedProperties='Excel8.0;HDR=YES;IMEX=1';
  9. conne.openconnStre
  10. Sqle=select*from[sheet1$]
  11. Setrse=Server.CreateObject(ADODB.Recordset)
  12. rse.opensqle,conne,1,1
  13. 'verify
  14. hang=2
  15. dowhilenottrse.eof
  16. 'The name cannot be empty
  17. iftrim(rse(0))<>then
  18. else
  19. mess=The name of the &hang& line is empty, please check!
  20. response.Write<script>alert('&mess&').window.location.href='updateFloor.asp'</script>
  21. response.End()
  22. endif
  23. rse.movenext
  24. hang=hang+1
  25. loop
  26. rse.movefirst
  27. dowhilenottrse.eof
  28. setrst=server.CreateObject(adodb.recordset)
  29. sqlt=select*fromSellman
  30. rst.opensqlt,conn,1,3
  31. rsst.addnew()
  32. rst(CompanyName)=c2(rse(0))
  33. rst(CompanyInfo)=c2(rse(1))
  34. rst(address)=c2(rse(2))
  35. rst(tel)=c2(rse(3))&&c2(rse(7))
  36. rst(Fax)=c2(rse(4))
  37. rst(linkman)=c2(rse(5))
  38. rst(Homepage)=c2(rse(8))
  39. rst(Email)=c2(rse(6))
  40. rsst.update()
  41. rsst.close
  42. setrst=nothing
  43. rse.movenext
  44. loop
  45. rse.close
  46. settrse=nothing
  47. response.Write<script>alert('Imported successfully!');location.href='updateFloor.asp';</script>

In fact, simply put it simply like an access database, open the excel file, read and write it to access. If you want to write it to SQL Server, change the writing process and it will be

Look at the code:

  1. dimconn
  2. dimconn2
  3. setconn=CreateObject(ADODB.Connection)
  4. conn.OpenProvider=Microsoft.Jet.OLEDB.4.0;JetOLEDB:DatabasePassword=;DataSource=c:/book1.mdb
  6. setconn2=CreateObject(ADODB.Connection)
  7. conn2.OpenProvider=Microsoft.Jet.OLEDB.4.0;JetOLEDB:DatabasePassword=;Extendedproperties=Excel5.0;DataSource=c:/book1.xls
  10. sql=SELECT*FROM[Sheet1$]
  11. setrs=conn2.execute(sql)
  12. whilenotrs.eof
  13. sql=insertintoxxx([a],[b],[c],[d])values('&fixsql(rs(0))&','&fixsql(rs(1))&','&fixsql(rs(2 ))&','&fixsql(rs(3))&')
  14. conn.execute(sql)
  15. rs.movenext
  16. wend
  18. conn.close
  19. setconn=nothing
  20. conn2.close
  21. setconn2=nothing
  23. functionfixsql(str)
  24. dimnewstr
  25. newsstr=str
  26. ifisnull(newstr)then
  27. newsstr=
  28. else
  29. newsstr=replace(newstr,','')
  30. endif
  31. fixsql=newstr
  32. endfunction

Related Articles