u need to just read exel file in classic asp with recordset and the u just insert all recored in oracle as u insert noramally if u wnat to insert all in one go then convert recordset data to xml and pass the xml to oracle and insert all row in one go.
you have to first upload the file to the server, then you can
use the code provided above.
to upload the file, look for something known as "ASP Upload".
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/mgma.asp" -->
<%
Dim rsAdd
Dim rsAdd_numRows
Set rsAdd = Server.CreateObject("ADODB.Recordset")
rsAdd.ActiveConnection = MM_mgma_STRING
rsAdd.Source = "SELECT * FROM tACADRAWDATA"
rsAdd.CursorType = 0
rsAdd.CursorLocation = 2
rsAdd.LockType = 3
rsAdd.Open()
rsAdd.AddNew
rsAdd_numRows = 0
%>
<%
Dim rsYear
Dim rsYear_numRows
Set rsYear = Server.CreateObject("ADODB.Recordset")
rsYear.ActiveConnection = MM_mgma_STRING
rsYear.Source = "SELECT * FROM tYEAR"
rsYear.CursorType = 0
rsYear.CursorLocation = 2
rsYear.LockType = 1
rsYear.Open()
rsYear_numRows = 0
%>
<%
Dim rsDatatype
Dim rsDatatype_numRows
Set rsDatatype = Server.CreateObject("ADODB.Recordset")
rsDatatype.ActiveConnection = MM_mgma_STRING
rsDatatype.Source = "SELECT * FROM tDATATYPE"
rsDatatype.CursorType = 0
rsDatatype.CursorLocation = 2
rsDatatype.LockType = 1
rsDatatype.Open()
rsDatatype_numRows = 0
%>
<%
Dim rsPhytype
Dim rsPhytype_numRows
Set rsPhytype = Server.CreateObject("ADODB.Recordset")
rsPhytype.ActiveConnection = MM_mgma_STRING
rsPhytype.Source = "SELECT * FROM tPHYTYPE"
rsPhytype.CursorType = 0
rsPhytype.CursorLocation = 2
rsPhytype.LockType = 1
rsPhytype.Open()
rsPhytype_numRows = 0
%>
<html>
<link href="mgma.css" rel="stylesheet" type="text/css">
<body>
<%
Dim form_val
form_val = Request.Querystring("key_word")
if (form_val <> "") Then
Dim objConn, objRS, strSQL
Dim xIndex, curValue, arrMy, arrTemp, varYear, varTotrows
Dim temp_date
temp_date = now()
varYear = YEAR(now)
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_
"Excel 8.0; DBQ=" & Server.MapPath("EXCEL_REPORTS\Academic_data.xls") & "; "
strSQL = "SELECT * FROM A2:Q10000"
Set objRS=objConn.Execute(strSQL)
varTotrows = objRS.UsedRange.Row.Count
Response.Write("<table valign=center width=100% cellpadding=0 cellspacing=0>")
Response.Write("<tr>")
For x=0 To objRS.Fields.Count-1
Response.Write("<th>" & objRS.Fields(x).Name & "</th>")
Next
Response.Write("<th>YEAR</th>")
Response.Write("</tr>")
Do Until objRS.EOF
'rsAdd.MoveFirst
Response.Write("<tr>")
For xIndex=0 To objRS.Fields.Count-1
curValue = objRS.Fields(xIndex).Value
If IsNull(curValue) Then
curValue="N/A"
End If
arrTemp = arrTemp & "," & curValue
curValue = CStr(curValue)
Response.Write("<td>" & curValue & "</td>")
Next
Response.Write("<td>")
Response.Write(year(now()))
Response.Write("</td>")
Response.Write("</tr>")
arrTemp = arrTemp &","& varYear
arrMy = split(arrTemp, ",")
For xIndex=1 to rsAdd.Fields.Count-1
rsAdd.Fields(xIndex).value=arrMy(xIndex)
next
arrTemp = ""
'rsAdd.AddNew
objRS.MoveNext
Loop
objRS.Close
Response.Write("</table>")
objConn.Close
Set objRS=Nothing
Set objConn=Nothing
else
%>
<table width="90%" border="0" cellpadding="0">
<tr>
<td width="15%" height="156" valign="top">
<table width="130" border="0" cellpadding="5">
<tr>
<td align="left" scope="col">LOAD NEW FILE
</td>
</tr>
<tr>
<td align="left" scope="col">DISPLAY RAW DATA
</td>
</tr>
<tr>
<th scope="col">
</th>
</tr>
</table>
</td>
<td width="85%" valign="top">
<P>
PLEASE ENTER THE FILE THAT YOU WANT TO LOAD IN TO THE DATABASE.
</P>
<form name="form1" method="post" action="readxls.asp?key_word=UPLOAD_DB">
<table width="80%" border="0" align="center" cellpadding="5">
<tr>
<td width="50%" scope="col">
<span class="style1">
<label>SELECT PHYSICIAN TYPE </label>
</span> </td>
<td width="50%" scope="col">
<select name="select" class="select-type1">
<%
While (NOT rsPhytype.EOF)
%>
<option value="<%=(rsPhytype.Fields.Item("PHYTYPE").Value)%>"><%=(rsPhytype.Fields.Item("PHYTYPE").Value)%>
</option>
<%
rsPhytype.MoveNext()
Wend
If (rsPhytype.CursorType > 0) Then
rsPhytype.MoveFirst
Else
rsPhytype.Requery
End If
%>
</select>
</td>
</tr>
<tr>
<td>
<span class="style1">
<label>SELECT COMPENSATION / WORK RVU DATA
</label>
</span>
</td>
<td>
<select name="select" class="select-type1">
<%
While (NOT rsDatatype.EOF)
%>
<option value="<%=(rsDatatype.Fields.Item("DATATYPE").Value)%>"><%=(rsDatatype.Fields.Item("DATATYPE").Value)%>
</option>
<%
rsDatatype.MoveNext()
Wend
If (rsDatatype.CursorType > 0) Then
rsDatatype.MoveFirst
Else
rsDatatype.Requery
End If
%>
</select>
</td>
</tr>
<tr>
<td>
<span class="style1">
<label>SELECT YEAR
</label>
</span>
</td>
<td>
<select name="select" class="select-type1">
<%
While (NOT rsYear.EOF)
%>
<option value="<%=(rsYear.Fields.Item("YEAR").Value)%>"><%=(rsYear.Fields.Item("YEAR").Value)%>
</option>
<%
rsYear.MoveNext()
Wend
If (rsYear.CursorType > 0) Then
rsYear.MoveFirst
Else
rsYear.Requery
End If
%>
</select>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" name="Submit" value="SUBMIT">
</td>
</tr>
</table>
</form> <p> </p>
</td>
</tr>
</table>
</body>
</html>
<%
End If
%>
<%
rsYear.Close()
Set rsYear = Nothing
%>
<%
rsDatatype.Close()
Set rsDatatype = Nothing
%>
<%
rsPhytype.Close()
Set rsPhytype = Nothing
%>
<%
Set rsAdd = Nothing
'rsAdd.Close()
%>