CLASSIC
ASP > A COMPLETE ASP CODE > FOR MIGRATING AN EXCEL WORKSHEET INTO
ORACLE TABLE
<%
Set MyConn =Server.CreateObject("ADODB.Connection")
MyConn.open="provider=MSDAORA;data source=xe; user id=scott;password=tiger"
'in the above line "xe" is the TNS Service name
Set ConnExcel=Server.CreateObject("ADODB.Connection")
ConnExcel.Provider="Microsoft.jet.oledb.4.0"
ConnExcel.Properties("Extended Properties").value ="Excel
8.0"
ConnExcel.open Server.Mappath("emp.xls")
excel_table_name="[Sheet1$]"
'Sheet1
is the worksheet name given at the bottom of excel file
'in the above line, "empworksheet" is the worksheet name in
the excel file
Set ObjRs=Server.CreateObject("adodb.recordset")
sql="Select * from " & excel_table_name
ObjRs.open sql,ConnExcel,adOpenStatic
If ObjRs.eof Then
Response.write ("no records present in excel file")
Else
sql="select * from emp"
Set ObjRsOra=Server.CreateObject("adodb.recordset")
ObjRsOra.cursorLocation=3
ObjRsOra.open sql,myconn,1,3
While not ObjRs.eof
ObjRsOra.AddNew
ObjRsOra("emp_id")=ObjRs("emp_id")
ObjRsOra("emp_name")=ObjRs("emp_name")
ObjRsOra.Update
response.write(objRs("emp_id") & " entered successfully<br
/>")
ObjRs.Movenext
Wend
End If
%>
|
