| Upload Excel Sheet in Classic ASP |
| suresh G posted at Wednesday, November 19, 2008 6:30 AM |
Hi
I need to upload the Excel sheet into Oracle while Uploading time I need handle all kind of validations,Transactions and failure records logger.
can anyone help me on this.
Thanks in advance
Thanks
Suresh
|
 |
|
|
| |
| Re :: Upload Excel sheet to oracle using Classic Asp |
| Sanjay Verma replied to suresh G at Wednesday, November 19, 2008 6:43 AM |
 | See the following article
http://classicasp.aspfaq.com/forms/how-do-i-upload-files-from-the-client-to-the-server.html
http://www.aspupload.com/manual_simple.html
Hope it helps. |
|
| |
 re |
| Web star replied to suresh G at Wednesday, November 19, 2008 6:44 AM |
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()
%> |
|
| |
| READ THIS LINK |
| C_A P replied to suresh G at Wednesday, November 19, 2008 6:50 AM |
| http://classicasp.aspfaq.com/forms/how-do-i-upload-files-from-the-client-to-the-server.html |
|
| |
| TRY THIS CODE |
| C_A P replied to suresh G at Wednesday, November 19, 2008 6:51 AM |
Code:
<% Option Explicit %>
<html>
<body>
<%
Dim objConn, objRS, strSQL
Dim x, curValue
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_
"Excel 8.0; DBQ=" & Server.MapPath("excelfile.xls") & "; "
strSQL = "SELECT * FROM A1:Q10000"
Set objRS=objConn.Execute(strSQL)
Response.Write("<table border=""1"">")
Response.Write("<tr>")
For x=0 To objRS.Fields.Count-1
Response.Write("<th>" & objRS.Fields(x).Name & "</th>")
Next
Response.Write("</tr>")
Do Until objRS.EOF
Response.Write("<tr>")
For x=0 To objRS.Fields.Count-1
curValue = objRS.Fields(x).Value
If IsNull(curValue) Then
curValue="N/A"
End If
curValue = CStr(curValue)
Response.Write("<td>" & curValue & "</td>")
Next
Response.Write("</tr>")
objRS.MoveNext
Loop
objRS.Close
Response.Write("</table>")
objConn.Close
Set objRS=Nothing
Set objConn=Nothing
%>
</body>
</html>
|
|
| |
| Form to upload data from Excel file to SQL 2000 database |
| C_A P replied to suresh G at Wednesday, November 19, 2008 6:52 AM |
| Here is sample code behind that I created few days ago. It imports data from excel to 2005 should be same for 2000 I belive.
If Not Me.FileUpload1.PostedFile Is Nothing And FileUpload1.PostedFile.ContentLength > 0 Then Dim fn As String = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName)
Dim SaveLocation As String = Server.MapPath("Data") & "\" & fn (point to folder where you want to save the data)
Try
FileUpload1.PostedFile.SaveAs(SaveLocation) Dim excelConnectionString As String = ("Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.MapPath("Data/" & fn) _ & ";" & "Extended Properties=Excel 8.0;")
Using connection As OleDbConnection = New OleDbConnection(excelConnectionString) Dim command As OleDbCommand = New OleDbCommand("Select * FROM [Sheet1$]", connection) (This will query on excel Sheet1 change name if name is different if name mismact it will not work)
Try
connection.Open() Catch ex As Exception
Response.Write("Error: " & ex.Message)
End Try
' Create DbDataReader to Data Worksheet Using dr As System.Data.OleDb.OleDbDataReader = command.ExecuteReader()
' SQL Server Connection String
Dim sqlConnectionString As String = "Data Source=yourserver;Initial Catalog=your databse;Persist Security Info=True;User ID=username;Password=password"
' Bulk Copy to SQL Server Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(sqlConnectionString)
bulkCopy.DestinationTableName = "database table name where you want to copy"
bulkCopy.WriteToServer(dr)
connection.Close()
File.Delete(SaveLocation)
End Using
End Using
End Using Catch Exc As Exception
Response.Write("Error: " & Exc.Message)
End Try
Else Response.Write("Please select a file to upload.")
End If
lblMessage.Text = "The file has been uploaded and inserted to the Database Successfully." |
|
| |
| also try this |
| M S replied at Tuesday, April 07, 2009 7:27 PM |
| http://www.sandboxm.com/excel-and-aspnet/ |
|
| |
|
|