How to use ASP & Upload excel import to database (Excel.Application)

Author: admin | Posted: 24-04-2010 | Category: ASP & Excel (Excel.Application)

How to use ASP & Upload excel import to database (Excel.Application) This is learn/tutorial asp developers how to using ASP script Upload excel and import to database.

ShotDev Focus:
- ASP & Upload excel and import to database.

Example

asp_excel_upload_import1.asp


<%Option Explicit%>
<html>
<head>
<title>ShotDev.Com Tutorial</title>
</head>
<body>
<form action="asp_excel_upload_import2.asp" method="post" enctype="multipart/form-data" name="frmMain">
Upload
<input name="file1" type="file">
<input type="submit" name="Submit" value="Submit">
</form>
</body>
</html>

asp_excel_upload_import2.asp


<%Option Explicit%>
<html>
<head>
<title>ShotDev.Com Tutorial</title>
</head>
<body>
<%
Dim xlApp,xlBook,xlSheet1,xlSheet2,OpenFile,i
Dim Conn,strSQL,objExec
Dim mySmartUpload
Dim sFileName

'*** Upload By aspSmartUpload ***'

'*** Create Object ***'
Set mySmartUpload = Server.CreateObject("aspSmartUpload.SmartUpload")

'*** Upload Files ***'
mySmartUpload.Upload

'** Getfile Name ***'
sFileName = mySmartUpload.Files("file1").FileName

If sFileName <> "" Then

mySmartUpload.Files("file1").SaveAs(Server.MapPath("MyXls/"&sFileName))

OpenFile = "MyXls/"&sFileName

'*** Create Exce.Application ***'
Set xlApp = Server.CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(Server.MapPath(OpenFile))
Set xlSheet1 = xlBook.Worksheets(1)

Set Conn = Server.Createobject("ADODB.Connection")
Conn.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("MyXls/mydatabase.mdb"),"" , ""

For  i = 2 To 5
If Trim(xlSheet1.Cells.Item(i,1)) <> "" Then
strSQL = ""
strSQL = strSQL &"INSERT INTO customer2 "
strSQL = strSQL &"(CustomerID,Name,Email,CountryCode,Budget,Used) "
strSQL = strSQL &"VALUES "
strSQL = strSQL &"('"&xlSheet1.Cells.Item(i,1)&"','"&xlSheet1.Cells.Item(i,2)&"', '"&xlSheet1.Cells.Item(i,3)&"' "
strSQL = strSQL &",'"&xlSheet1.Cells.Item(i,4)&"','"&xlSheet1.Cells.Item(i,5)&"', '"&xlSheet1.Cells.Item(i,6)&"') "
Set objExec = Conn.Execute(strSQL)
Set objExec = Nothing
End IF
Next

xlApp.Application.Quit

'*** Quit and Clear Object ***'
Conn.Close()
Set Conn = Nothing
Set xlSheet1 = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End If

Set mySmartUpload = Nothing
%>
Data Import/Inserted <a href="MyXls/mydatabase.mdb">Click here</a> to Download.
</body>
</html>

Create a asp file and save to path root-path/myasp/

Run
http://localhost/myasp/asp_excel_upload_import1.asp

Screenshot