Last time provided a method of importing from an Excel spreadsheet into a SQL Server database, provided that the EXCEL component on the machine is installed. Today, I will provide another way: don't need to install Excel, you can also import our SQL Server database. First use SQL Server's own data conversion function to import Excel into SQL Server, the table name after importing in the example below: Sheet1 $, then we will import data into our own system data sheet. . (This is the problem of data transition in 2 SQL Server tables). In short, the principle of 2 methods is to read all the data from a data source, then insert the source data into our target table by loop. The full code is as follows:
<% 'On Error Resume Next' imports Excel spreadsheet data to SQL Sever Database by Dicky 2004-12-27 16: 41: 12Function Open_CONN (SqlDatabaseEname, SqlPassword, SQLUSERNAME, SQLLOCALNAME, SQLCONN) Create a database connection object and open database connections DIM ConnStr 'SQL Server Database Connection Parameters: Database Name, User Password, User Name, Connection Name (Local Local, IP) Connstr = "Provider = SQLOLDB; 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) 'Closing the Database Connection and Clears Database Connection Object If IsObject (Sqlconn) Then Sqlconn.close Set Sqlconn = Nothing End IFEND Function
Call Open_CONN ("Shat_edG", "," SA ",", "," SQLCONN) Opens the local SQL Server database connection CALL OPEN_CONN ("Shat_edg", "SA", "SSH03", SQLCONN1) ' Open Remote SQL Server Database Connections
Function Get_EMP_CnName (NTACCNT) 'obtained according to the user NT user account name Chinese 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 xi = nothingnd function%>
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 the data source (Local Library): <select name = "Table" Title = "Please select the table that needs to import data"> <option> </ option> <% DIM RSSQLDATABASETABLE SET RSSQLDATABASETABLE SET RSSQLDATABASETABLE SET RSSQLDATABASetable = SqlConn.Openschema 20) Do WHILE NOT RSSQLDATABASETABLE.EOF%> <option <% IF TRIM (Request ("Table")) = rssqldatabaseetable (2) Then Response.write "SELECTED"% >> <% = rssqldatabaseetable (2)%> </ Option> <% rssqldatabaseetable.movenext: loop set rssqldatabaseetable = not%> </ select> <input type = submit name = Submit value = "Start Export"> <br> Select Target Table (Remote Library): <SELECT NAME = "Table1" Title = "Please select the table that needs 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 "% >> <% = rssqldatabaseetable1 (2)%> </ option> <</p>
<p>% Rssqldatabaseetable1.movenext: loop set = nothing%> </ select> </ form> <font color = blue> Do not refresh the page during the export! </ font> <br> <% IF TRIM (Request ("Table1")) <> "" "" SETEN DIM SQL, RS SQL = "SELECT * from Sheet1 $" 'Sheet1 $ is our implementation of SQL Server self-data conversion function Get the table name SET RS = SQLCONN.EXECUTE (SQL) if xi f r e a "Did not find the data you need !! <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_Director, EDG_Project_Director_CnName) Values ( ' "& Trim (Rs (" project name ")) &"', ' "& Trim (RS ("Engineering Number") & Trim (RS ("Project Deputy Union)) &" ',' "& Trim (RS (" Project Deputy Union)) & "(" & Get_Emp_cnname (Trim (RS ("Project Deputy")) & ")" & "'" & TRIM (RS ("Project Manager") & "" "& TRIM (RS (" Project Manager ") &" "& GET_EMP_CNNAME (TRIM (" Project Manager ")) &") "&" '"& TRIM (RS (" Project Director ") &"' "& TRIM (RS (" Project Director ")) & "(" & GET_EMP_CNNAME (TRIM ("Project Director")) & ")" "&") "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" " Insert all Dong Rs.Movenext loop response.write "<font color = red>, successfully exported data! ^ _ ^ </ Font> <br>" Endhingndiff IF</p></div><div class="text-center mt-3 text-grey">
转载请注明原文地址:https://www.9cbs.com/read-75063.html</div><div class="plugin d-flex justify-content-center mt-3"></div><hr><div class="row"><div class="col-lg-12 text-muted mt-2"><i class="icon-tags mr-2"></i><span class="badge border border-secondary mr-2"><h2 class="h6 mb-0 small"><a class="text-secondary" href="tag-2.html">9cbs</a></h2></span></div></div></div></div><div class="card card-postlist border-white shadow"><div class="card-body"><div class="card-title"><div class="d-flex justify-content-between"><div><b>New Post</b>(<span class="posts">0</span>)
</div><div></div></div></div><ul class="postlist list-unstyled">
</ul></div></div><div class="d-none threadlist"><input type="checkbox" name="modtid" value="75063" checked /></div></div></div></div></div><footer class="text-muted small bg-dark py-4 mt-3" id="footer"><div class="container"><div class="row"><div class="col">CopyRight © 2020 All Rights Reserved
</div><div class="col text-right">Processed:
<b>0.037</b>, SQL:
<b>9</b></div></div></div></footer><script src="./lang/en-us/lang.js?2.2.0"></script><script src="view/js/jquery.min.js?2.2.0"></script><script src="view/js/popper.min.js?2.2.0"></script><script src="view/js/bootstrap.min.js?2.2.0"></script><script src="view/js/xiuno.js?2.2.0"></script><script src="view/js/bootstrap-plugin.js?2.2.0"></script><script src="view/js/async.min.js?2.2.0"></script><script src="view/js/form.js?2.2.0"></script><script>
var debug = DEBUG = 0;
var url_rewrite_on = 1;
var url_path = './';
var forumarr = {"1":"Tech"};
var fid = 1;
var uid = 0;
var gid = 0;
xn.options.water_image_url = 'view/img/water-small.png';
</script><script src="view/js/wellcms.js?2.2.0"></script><a class="scroll-to-top rounded" href="javascript:void(0);"><i class="icon-angle-up"></i></a><a class="scroll-to-bottom rounded" href="javascript:void(0);" style="display: inline;"><i class="icon-angle-down"></i></a></body></html><script>
var forum_url = 'list-1.html';
var safe_token = 'h7GQvN34lbroaV9hrB6tNEKqznLVDt41CHWP_2F24ZkUCwg2goxhmr0lGRJUDg1cKdP7Lkhpm29CcQl6DVtd7mig_3D_3D';
var body = $('body');
body.on('submit', '#form', function() {
var jthis = $(this);
var jsubmit = jthis.find('#submit');
jthis.reset();
jsubmit.button('loading');
var postdata = jthis.serializeObject();
$.xpost(jthis.attr('action'), postdata, function(code, message) {
if(code == 0) {
location.reload();
} else {
$.alert(message);
jsubmit.button('reset');
}
});
return false;
});
function resize_image() {
var jmessagelist = $('div.message');
var first_width = jmessagelist.width();
jmessagelist.each(function() {
var jdiv = $(this);
var maxwidth = jdiv.attr('isfirst') ? first_width : jdiv.width();
var jmessage_width = Math.min(jdiv.width(), maxwidth);
jdiv.find('img, embed, iframe, video').each(function() {
var jimg = $(this);
var img_width = this.org_width;
var img_height = this.org_height;
if(!img_width) {
var img_width = jimg.attr('width');
var img_height = jimg.attr('height');
this.org_width = img_width;
this.org_height = img_height;
}
if(img_width > jmessage_width) {
if(this.tagName == 'IMG') {
jimg.width(jmessage_width);
jimg.css('height', 'auto');
jimg.css('cursor', 'pointer');
jimg.on('click', function() {
});
} else {
jimg.width(jmessage_width);
var height = (img_height / img_width) * jimg.width();
jimg.height(height);
}
}
});
});
}
function resize_table() {
$('div.message').each(function() {
var jdiv = $(this);
jdiv.find('table').addClass('table').wrap('<div class="table-responsive"></div>');
});
}
$(function() {
resize_image();
resize_table();
$(window).on('resize', resize_image);
});
var jmessage = $('#message');
jmessage.on('focus', function() {if(jmessage.t) { clearTimeout(jmessage.t); jmessage.t = null; } jmessage.css('height', '6rem'); });
jmessage.on('blur', function() {jmessage.t = setTimeout(function() { jmessage.css('height', '2.5rem');}, 1000); });
$('#nav li[data-active="fid-1"]').addClass('active');
</script>
