|
Last time, I provided you with a method to import data from an Excel spreadsheet to an SQL Server database, provided that the Excel component in the office is installed on the machine. Another method is provided for you today: You can import data to our SQL Server database without installing excel. First, use the data conversion function of SQL Server to import EXCEL to SQL Server. In the following example, the imported table name is sheet1 $, then we can import the data from this database to the data table of our own system. (This is the problem of data conversion between two SQL Server tables ). In short, the principle of the two methods is to first read all the data from a data source, and then insert the source data into our target table through a loop. CompleteCodeAs follows: <%
''On error resume next
''Import Excel spreadsheet data to SQL Server database by Dicky 16:41:12
Function open_conn (sqldatabasename, sqlpassword, sqlusername, sqllocalname, sqlconn)
''Create a database connection object and open a database connection
Dim connstr
''SQL Server database connection parameters: Database Name, user password, user name, connection name (local for local use, IP for foreign use)
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) ''to connect to the local SQL Server database
Call open_conn ("shat_edg", "", "sa", "ssh03", sqlconn1) ''To open a remote SQL Server database connection Function get_emp_cnname (ntaccnt)
''Obtain the Chinese name of the user based on the user's 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 %>
<HTML>
<Head>
<Title> Import Excel spreadsheet data to the SQL Server database </title>
<Body bgcolor = "# acd9af">
<Center> <B> Import Excel spreadsheet data to SQL Server database </B> </center>
<Form method = "Post" name = "form1">
Select a data source (local database ):
<Select name = "table" Title = "select the table to import data">
<Option> </option>
<% Dim rssqldatabasetable
Set rssqldatabasetable = sqlconn. openschema (20)
Do while 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 = "Start Time Output">
<Br> select the target table (remote database ):
<Select name = "Table1" Title = "select the table to import data">
<Option> </option>
<% Dim rssqldatabasetable1
Set rssqldatabasetable1 = sqlconn1.openschema (20)
Do while 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> do not refresh the hosts during the upload process! </Font> <br>
<%
If trim (Request ("Table1") <> "then
Dim SQL, RS
SQL = "select * From sheet1 $" ''sheet1 $ is the name of the table we obtained by using the SQL server data conversion function.
Set rs = sqlconn. Execute (SQL)
If Rs. EOF and Rs. bof then
Response. write "the data you need is not found !! <Br>"
Else
Do while 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_ctor ctor, edg_project_director_cnname) values (''" & trim (RS ("project name "))&"'', ''" & trim (RS ("Project No.") & "'', ''" & trim (RS ("Project Assistant "))&"'', ''" & trim (RS ("Project Assistant") & "(" & 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 ")))&")"&"'')"
''Response. Write sqlinsert
''The inserted value is adjusted based on the actual database structure.
Sqlconn1.execute (sqlinsert) ''insert an Excel table to show all things
Rs. movenext
Loop
Response. Write "<font color = Red> congratulations! data is successfully exported! ^ _ ^ </Font> <br>"
End if Rs. Close
Set rs = nothing
End if Call close_conn (sqlconn) ''to close the Excel database connection
Call close_conn (sqlconn1) ''To disable SQL Server database connection %>
</Body>
</Html> |
