Upload Excel Sheet in Classic ASP

Asked By suresh G

19-Nov-08 06:30 AM

Earn up to 0 extra points for answering this tough question.

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

Shailendrasinh Parmar replied to suresh G

19-Nov-08 06:43 AM

Reply

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

19-Nov-08 06:44 AM

Reply

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"%>

<%
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

19-Nov-08 06:50 AM

Reply

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

19-Nov-08 06:51 AM

Reply

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

19-Nov-08 06:52 AM

Reply

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

Catch Exc As Exception

Response.Write("Error: " & Exc.Message)

End Try

Else

Response.Write("Please select a file to upload.")