excel| Spreadsheet | data | database <%
' On Error Resume Next
' Import Excel spreadsheet data to SQL Sever database by Dicky 2004-12-27 16:41:12 qq:25941
Function Open_conn (Sqldatabasename,sqlpassword,sqlusername,sqllocalname,sqlconn)
' Create a database connection object and open the database connection
Dim ConnStr
' SQL Server database connection parameters: Database name, user password, username, connection name (local locally, field IP)
ConnStr = "PROVIDER=SQLOLEDB; User id= "& Sqlusername &"; Password= "& SQLPassword &"; Initial Catalog = "& Sqldatabasename &"; Data source= "& Sqllocalname &";
Set sqlconn = Server.CreateObject ("ADODB. Connection ")
Sqlconn.open ConnStr
If ERR Then
Err.Clear
Set sqlconn = Nothing
Response.Write "Data connection Error!"
Response.End
End If
End Function
Function Close_conn (sqlconn)
' Close the database connection and clear the database connection object
If IsObject (sqlconn) Then
Sqlconn.close
Set sqlconn = Nothing
End If
End Function
Call Open_conn ("Shat_edg", "", "sa", "(local)", sqlconn) ' Open SQL Server database connection
Function Get_emp_cnname (ntaccnt)
' User's Chinese name according to user NT account
Dim Sql,rs
SQL = "Select Emp_cname from Rf_employee Where emp_ntaccnt= '" &NTACCNT& ""
Set Rs = Server.CreateObject ("Adodb.recordset")
Rs.Open sql,sqlconn,1,1
If rs.eof Then
Get_emp_cnname = ""
Else
Get_emp_cnname = Rs ("Emp_cname")
End If
Rs.close
Set Rs = Nothing
End Function%>
<title> import Excel spreadsheet data to SQL Sever database </title>
<body bgcolor= "#ACD9AF" >
<center><b> import Excel spreadsheet data to SQL Sever database </b></center>
<form method= "POST" Name= "Form1" >
Choose the data source: <input type= "file" name= "file" size= "title=" Please select the Excel file you want to guide > <input type=submit name=submit value = "Start out" >
<br> Please select the target list:
<select name= "table" title= "Please select the tables you want to import data >"
<option></option>
<% Dim rssqldatabasetable
Set rssqldatabasetable = Sqlconn.openschema (20)
Do as not rssqldatabasetable.eof%>
<option<%if Trim (Request ("Table")) =rssqldatabasetable (2) Then Response.Write "Selected"%>><%= Rssqldatabasetable (2)%></option>
<% RsSqlDatabaseTable.MoveNext:Loop
Set rssqldatabasetable = Nothing%>
</SELECT>
</FORM>
<font color=blue> The process please do not refresh the pages! </font><br>
<%
If Right (Trim (Request ("File"), 3) = "xls" Then
Response.Write "<font color=red> your Excel file is:" &trim (Request ("File") & "</font><br>"
End If
' =====================asp read Excel notes =====================================
' I "Excel97 or Excel2000 generated xls file (book) as a database, where each worksheet (sheet) as a database table
' II ADO assumes the first behavior field name in Excel. So the scope of your definition must include the first line of content
' III ' the row header (that is, the field name) in Excel cannot contain numbers. Excel drivers can make mistakes when encountering this problem. For example, your row header is named "F1."
' IIII If you have a column in your Excel spreadsheet that contains both text and numbers, then Excel's ODBC driver will not be able to handle this line of data types, you have to ensure that the data type of the column is consistent
' E-mail:kaxue@hotmail.com qq:484110 HOMEPAGE:www.Flyday.net
' Finishing Time: Thursday, May, 2002 5:54 PM WIN2000SERVER+IIS5-Test Pass
'============================================================================
If Trim (Request ("File") = "" Then
Response.Write "<font color=red>, please select the Excel file you need to guide!" </font> "
Response.End
ElseIf Right (the Trim (Request ("File"), 3) <> "XLS" Then
Response.Write "<font color=red> is sorry, please make sure you want to guide the Excel file!" </font> "
Response.End
Else
' List all worksheets in the Excel file that you select
Dim Execlfile,objexcelapp,objexcelbook
Execlfile = Trim (Request ("File")
Set Objexcelapp = CreateObject ("Excel.Application")
Objexcelapp.quit
Objexcelapp.displayalerts = False ' does not show warning
objExcelApp.Application.Visible = False ' does not display interface
ObjExcelApp.WorkBooks.Open (Execlfile)
Set Objexcelbook = Objexcelapp.activeworkbook
ReDim Arrsheets (ObjExcelBook.Sheets.Count)
For I=1 to ObjExcelBook.Sheets.Count
Arrsheets (i) = Objexcelbook.sheets (i). Name
' Response.Write arrsheets (i)
Next
Objexcelapp.quit
Set Objexeclapp = Nothing
' List all worksheets in the Excel file that you select
End If
Dim Conn,driver,dbpath,rs,sqlinsert,sqldelete
' Create a Connection object
Set Conn = Server.CreateObject ("ADODB. Connection ")
Driver = "Driver={microsoft Excel Drive
