The way to upload Excel data to SQL Server requires a three point notice!
Note: To upload Excel data to SQL Server, Excel must be uploaded to the server in advance.
Practice: In the asp.net environment, add a fileupload upload control
e.x of the background code:
Copy Code code as follows:
if (fileupload1.hasfile)//If the user does select the browse button for the upload control, the browse file succeeds.
{
This. Fileupload1.saveas ("e:\\temp\\" + fileupload1.filename); The directory saved to the server needs to be modified according to the server specifics Fileupload1.filename will automatically get the upload file name.
}
Ok successfully uploaded to the server in the E:\Temp directory (write their own attention to error handling, and exception handling Ah, very important).
Note that the structure (field) of the tables in the SQL Server server is the same as the order of Excel formats that you want to come up.
The key code to save to the server is the following e.x (SQL statement):
Copy Code code as follows:
INSERT INTO Employeescheck select * from OPENROWSET (' MICROSOFT. JET. oledb.4.0 ', ' Excel 8.0;hdr=yes;database=e:\\temp\\ ' + fileupload1.filename + "', teenie$)
Employeescheck is my data acceptance table in SQL SERVER, Hdr=yes represents the first row of Excel content as a content field instead of a field column
Teenie is the name of the current page of my Excel (note this must be written right, otherwise the words will be wrong, in addition to the name behind the home $ this thing)
To unblock sql:
SQL Server blocked access to component ' Ad Hoc distributed Queries ' STATEMENT ' openrowset/opendatasource ' because this component has been shut down as part of this server security configuration. System administrators can enable ' Ad Hoc distributed Queries ' by using sp_configure. For more information about enabling ' Ad Hoc distributed Queries ', see "Perimeter Configuration" in SQL Server Books Online.
Because SQL2005 does not open the ' Ad Hoc distributed Queries ' component by default, the opening method is as follows
Copy Code code as follows:
EXEC sp_configure ' show advanced options ', 1
Go
Reconfigure
Go
EXEC sp_configure ' Ad Hoc distributed Queries ', 1
Go
Reconfigure
Go
Haha, that's it. This is relatively simple to deal with the general application, because the general use of Excel data is the company's internal data processing, so completely enough to deal with, but if it is a commercial or professional site so that any ordinary users upload Excel words is not appropriate, After all, you can't strongly request all users Excel's table of data page names are called Teenie Oh, online there is a very troublesome SQL statements (are the form of parameters) can solve this problem, hehe, we have no harm to reference!
