excel| Spreadsheet | data | The database last provided you with a way to import from an Excel spreadsheet into a SQL Server database, provided that the Excel components in Office are installed on the machine. Here's another way to do it today: you don't need to install Excel or import to our SQL Server database. First, you import Excel into SQL Server with SQL Server's own data transformation capabilities, and the following example, the table named: sheet1$, then imports the data from the library into our own system's datasheet. (This is the problem with the conversion of data from 2 SQL Server tables). In short, the principle of 2 methods is to read all the data from a data source, and then through the loop, the source data into our target table.
The complete code is as follows:
<%
' On Error Resume Next
' Import Excel spreadsheet data to SQL Sever database by Dicky 2004-12-27 16:41:12
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 local SQL Server database connection
Call Open_conn ("Shat_edg", "", "sa", "ssh03", SqlConn1) ' Open remote SQL Server database connection
Function Get_emp_cnname (ntaccnt)
' User's Chinese name according to user NT account
Dim sql1,rs1
SQL1 = "Select Emp_cname from Rf_employee Where emp_ntaccnt= '" "&NTACCNT&" "
Set Rs1 = Server.CreateObject ("Adodb.recordset")
Rs1.open sql1,sqlconn1,1,1
If rs1.eof Then
Get_emp_cnname = ""
Else
Get_emp_cnname = RS1 ("Emp_cname")
End If
Rs1.close
Set Rs1 = 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" >
Please select data sources (local library):
<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><input type=submit name=submit value= "Started" >
<br> Please select the target list (remote library):
<select name= "Table1" title= "Please select table to import data" >
<option></option>
<% Dim RsSqlDatabaseTable1
Set RsSqlDatabaseTable1 = Sqlconn1.openschema (20)
Do as not rssqldatabasetable1.eof%>
<option<%if Trim (Request ("Table1")) =rssqldatabasetable1 (2) Then Response.Write "Selected"%>><%= RsSqlDatabaseTable1 (2)%></option>
<% RsSqlDatabaseTable1.MoveNext:Loop
Set RsSqlDatabaseTable1 = Nothing%>
</SELECT>
</FORM>
<font color=blue> The process please do not refresh the pages! </font><br>
<%
If Trim (Request ("Table1")) <> "Then
Dim Sql,rs
sql = "SELECT * from sheet1$" ' sheet1$ is the name of the table we implemented with SQL Server's own data conversion function
Set Rs = Sqlconn.execute (SQL)
If rs.eof and Rs.bof Then
Response.Write "did not find the data you need!! <br> "
Else
Do as not rs.eof
Sqlinsert = "Insert into" &trim (Request ("Table1")) & "(Edg_project_name,edg_project_no,edg_project_vm,edg_ Project_vm_cnname,edg_project_m,edg_project_m_cnname,edg_project_director,edg_project_director_cnname) Values (' "&trim (RS (" project name ")) &" ', ' "&trim (RS (" project number ")) &" ', ' "&trim (RS (" Project Assistant ") &" ', ' "&trim (RS (" Project Vice ") &" ("&get_emp_cnname (Trim (RS (" project Assistant)) "&") "&" ', ' "&trim (RS (" project Manager ")) &" ', ' "&trim (RS ("project Manager")) & "(" &get_emp_cnname (Trim (RS ("project manager))) &" "&" ', ' "&trim (RS (" project Director ")) &" ', ' " &trim (RS ("project Director") & "(" &get_emp_cnname "(Trim (RS (" project Director ")) &
