Excel table data is imported into the SQL Server database

There are many ways to import Excel table data into a SQL Server database, and here are just a few of them:

1, first, we have to create a new my_test table in the test database, the table has three fields Tid int type, tname nvarchar type, TT nvarchar type
(Note: The data type in the My_test table must match the type of the corresponding field in Excel)

2. We use SELECT * from OPENROWSET (' microsoft.jet.oledb.4.0 ', ' Excel 5.0;database=[excel table. xsl file path]; Hdr=yes;imex=1 ', Sheet1 to read the data in an Excel table, the data that is read is the same as the data that is read from the table in the database, and also includes the field name and data. Of course we can also use the field name list to get the department data in the Excel table. SELECT Field 1, Field 2, Field 3 [...] From OPENROWSET (' microsoft.jet.oledb.4.0 ', ' Excel 5.0;database=[excel table. xsl file path]; Hdr=yes;imex=1 ', Sheet1

Note: Hdr=yes, this represents the first row is the title, not used for data use; IMEX (IMport EXport mode) settings
There are three modes of IMEX:
0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)
What I want to highlight here is the IMEX parameter, because different patterns represent different reading and writing behaviors:
When imex=0 is "Export mode", the Excel file opened by this mode can only be used for "write" purposes.
When Imex=1 is "Import Mode", the Excel file opened in this mode can only be used for "read" purposes.
When imex=2 is "Nexus Mode", the Excel file opened in this mode can support both "read" and "write" purposes.
The meanings are as follows:
0---output mode;
1---input mode;
2----link mode (full update capability)

3, the first line in Excel is defined as the column name, starting from line 2nd is the data. The data that is read from Excel through the SQL statement also starts with the second row, and the two column name becomes the field name. If your first row has a column name defined, the name of each field in the data you get from Excel is the column name in Excel. For example, the field names of the data obtained from the Test.xls sheet table are numbered name notes respectively. If the first row of the Excel table you define does not have a column name defined, then the field names of the retrieved data are F1, F2, F3 ... And so on If you just want to get the data from some of the columns in the Excel table, you can use the above content.

4. Create a new Web Form (test.aspx) in VS2005, add a button control to it, and perform the import with a click of the button. Double-click the button to define the event handler function. The code in Test.aspx.cs is as follows:

usingSystem;usingSystem.Data;usingSystem.Configuration;usingSystem.Collections;usingsystem.web;usingSystem.Web.Security;usingSystem.Web.UI;usingSystem.Web.UI.WebControls;usingSystem.Web.UI.WebControls.WebParts;usingSystem.Web.UI.HtmlControls;usingSystem.Data.SqlClient; Public Partial classadmin_test:system.web.ui.page{protected voidPage_Load (Objectsender, EventArgs e) {    }     PublicSqlConnection con () {return NewSqlConnection ("server=localhost;uid=test;pwd=test;database=test"); //The test in the uid=test here must be system administrtor, or it will go wrong    }    protected voidButton1_click1 (Objectsender, EventArgs e) {SqlConnection Mycon=con (); stringSqlstr ="INSERT INTO My_test select number, name, Memo from OPENROWSET (' MICROSOFT. JET. oledb.4.0 ', ' Excel 5.0; Hdr=yes;database=e:\\test.xls ', sheet1$)";/*Here you can use * instead of numbers, names, notes, which represent column names in Excel*/SqlCommand cmd=NewSqlCommand (Sqlstr, mycon); Mycon.        Open (); Cmd.        ExecuteNonQuery (); Mycon.    Close (); }}

The following problem may occur when you execute the above code:

SQL Server blocked access to the STATEMENT ' openrowset/opendatasource ' of component ' Ad Hoc distributed Queries ' because this component was shut down as part of this server's security configuration. System administrators can enable ' Ad Hoc distributed Queries ' by using sp_configure. For more information about enabling ' Ad Hoc distributed Queries ', see "surface area Configurator" in SQL Server Books Online.

Workaround:

/* Enable ad hoc distributed queries:*/exec sp_configure ' show advanced options ', 1reconfigureexec sp_configure ' ad hoc distribu Ted Queries ', 1reconfigure/* After use is complete, close the ad hoc distributed queries:*/exec sp_configure ' ad hoc distributed Queries ', 0reconfigureexec sp_configure ' show advanced options ', 0reconfigure

Excel table data is imported into the SQL Server database