Home > Developer > SQL Server
<%
'On error resume next
'Import Excel spreadsheet data to SQL Server 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 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 open the SQL Server database connection
Function get_emp_cnname (ntaccnt)
'Obtain the Chinese name of the user based on the user's 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 %>
<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: <input type = "file" name = "file" size = "40" Title = "select the Excel file to be exported"> <input type = submit name = submit value = "Start outgoing">
<Br> Please select the target table:
<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>
</Form>
<Font color = blue> do not refresh the hosts during the upload process! </Font> <br>
<%
If right (TRIM (Request ("file"), 3) = "xls" then
Response. Write "<font color = Red> the Excel file you export is:" & trim (Request ("file") & "</font> <br>"
End if
'================================== ASP read Excel note ====================== ======================================
'I) Think Of The xls file (book) generated by excel97 or excel2000 as a database, and each Worksheet (sheet) in it as a database table.
Ii) ADO assumes the name of the first row in Excel. Therefore, you must include the content of the first row in the defined range.
'III) the row title (field name) in Excel cannot contain numbers. The Excel driver may encounter errors in this case. For example, your line title is "f1"
'Iiii) If a column in your Excel worksheet contains both text and numbers, the ODBC driver of Excel cannot process the data type of this row, make sure that the data type of this column is consistent.
'E-mail: Kaxue@Hotmail.com QQ: 484110 homepage: www.flyday.net
'Sorting time: Thursday, May 23,200 2 pm win2000server + iis5 passed the test
'================================================ ============================================
If trim (Request ("file") = "" then
Response. Write "<font color = Red> sorry, please select the Excel file to be exported! </Font>"
Response. End
Elseif right (TRIM (Request ("file"), 3) <> "xls" then
Response. Write "<font color = Red> sorry, please confirm that you want to export an Excel file! </Font>"
Response. End
Else
'List all worksheets in the selected Excel File
Dim execlfile, objexcelapp, objexcelbook
Execlfile = trim (Request ("file "))
Set objexcelapp = Createobject ("Excel. application ")
Objexcelapp. Quit
Objexcelapp. displayalerts = false' no warning is displayed
Objexcelapp. application. Visible = false' the interface is not displayed.
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 selected Excel File
End if
Dim Conn, driver, dbpath, RS, sqlinsert, sqldelete
'Create a connection object
Set conn = server. Createobject ("ADODB. Connection ")
Driver = "driver = {Microsoft Excel Driver (*. xls )};"
'Dbpath = "DBQ =" & server. mappath ("hrb.xls ")
Dbpath = "DBQ =" & trim (Request ("file "))
'Call the Open Method to open the database
Conn. Open driver & dbpath
'Dsn connection method
'Conn. Open "DSN = test"
'Note that the table name must be in the following format: "[Table Name $ ]"
For I = 1 to ubound (arrsheets) 'start to cycle all worksheets
SQL = "select * from [" & arrsheets (I) & "$]"
Set rs = conn. Execute (SQL)
If Rs. EOF and Rs. bof then
Response. write "the data you need is not found !! <Br>"
Else
'Response. Write "<font color = blue> Worksheet: </font> <font color = green>" & arrsheets (I) & "</font> <br>"
Do while not Rs. EOF
Sqlinsert = "insert into" & trim (Request ("table") & "(edg_project_name, edg_project_no, edg_project_vm, metadata, edg_project_m, edg_project_m_cnname, edg_project_ctor ctor, role) values ('"& trim (RS (0) &"', '"& trim (RS (1) &"', '"& trim (RS (2 )) & "','" & trim (RS (2) & "(" & get_emp_cnname (TRIM (RS (2 )))&")"&"', '"& trim (RS (3) &"', '"& trim (RS (3) &" ("& get_emp_cnname (TRIM (RS (3 ))) & ")" & "','" & trim (RS (4) & "','" & trim (RS (4 )) & "(" & get_emp_cnname (TRIM (RS (4 )))&")"&"')"
'The inserted value is adjusted based on the actual database structure.
Sqlconn. Execute (sqlinsert) 'insert an Excel table to show all things
Rs. movenext
Loop
Response. write "<font color = Red> congratulations, the worksheet in the Excel file is successfully merged [</font> <font color = blue>" & arrsheets (I) & "</font> <font color = Red>] [<font color = blue>" & trim (Request ("table ")) & "</font>! ^ _ ^ </Font> <br>"
End if
Rs. Close
Set rs = nothing
Next 'cycle the end of all worksheets
Call close_conn (conn) 'to close the Excel database connection
Call close_conn (sqlconn) 'Close SQL Server database connection %>
</Body>
</Html>
Similar to this:
How to import a client Excel table to a remote SQL Server
<%
'// Upload an Excel file from the client and save it as upload/excel.xls
.....
'// Open SQL Server
....
Conn. Open...
'// Open Excel
Set connexcel = server. Createobject ("ADODB. Connection ")
Excelstr = "DBQ =" + server. mappath ("upload/excel.xls") + "; driver = {Microsoft Excel Driver (*. xls )};"
Connexcel. Open excelstr
Set rsexcel = server. Createobject ("ADODB. recordset ")
Sqlexcel = "select * from [sheet1 $]" '// your sheet1 name
Rsexcel. Open sqlexcel, connexcel, 3, 3
Set rs = server. Createobject ("ADODB. recordset ")
SQL = "select * from [reguser]"
Rs. Open SQL, Conn, 1, 3
'----------------------------
'// Add begin
Do while not rsexcel. EOF
Rs. addnew
For I = 0 to rsexcel. Fields. Count-1
RS (I) = trim (rsexcel (I ))
Next
Rs. Update
Rsexcel. movenext
Loop
'// Add end
'----------------------------
Rsexcel. Close
Set rsexcel = nothing
Rs. Close ()
Set rs = nothing
%>
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
