Importing Excel table data to SQL Server database

C #. Import the data in the Excel table to the corresponding table in the SQL Server database in ASP. NET.
Here we will E: \ test.xlsMedium Sheet1The data in the table is imported to the test database. My_testTable.
Software: vs2005, SQL Server2000,
In test.xls, table sheet1 contains three fields: ID, name, and remarks. For details, see.

1,First, create a new my_test table in the test database. The table has three fields.
TIDInt type,TnameNvarchar type,TTNvarchar type
(Note: The data type in the my_test table must be the same as that in the corresponding field in Excel)

2,We use

Select * From OpenRowSet ('Microsoft. jet. oledb.4.0 ', 'excel 5.0; database = [Excel table. path of the XSL file]; HDR = yes; IMEX = 1', sheet1 $ ')

To read data from an Excel table. The read data is the same as that read from a database table, including the field name and data. Of course, you can also use the field name list to obtain 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. path of the XSL file]; HDR = yes; IMEX = 1', sheet1 $ ')

3,In Excel, the first row defines the column name, and data starts from row 2nd. The data read from Excel using SQL statements also starts from the second row, and the second column name is changed to the field name. If you have a column name defined in the first row, the names of the fields of the data obtained from Excel are the names of the columns in Excel. For example, the field names of the data obtained from the table test.xls sheet areNo. Name remarks. If the column name is not defined in the first row of the Excel table, the obtained data field names are F1, F2, F3. so on. If you only want to obtain data from some columns in an 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 click this button to execute import. Double-click the button to define the event processing function. The code in test. aspx. CS is as follows:

Using system;
Using system. Data;
Using system. configuration;
Using system. collections;
Using system. Web;
Using system. Web. Security;
Using system. Web. UI;
Using system. Web. UI. webcontrols;
Using system. Web. UI. webcontrols. webparts;
Using system. Web. UI. htmlcontrols;
Using system. Data. sqlclient;

Public partial class admin_test: system. Web. UI. Page
{
Protected void page_load (Object sender, eventargs E)
{

}
Public sqlconnection con ()
{
Return new sqlconnection ("Server = localhost; uid = test; Pwd = test; database = test ");
// The uid = test must be System administrtor. Otherwise, an error occurs.
}
Protected void button1_click1 (Object sender, eventargs E)
{
Sqlconnection mycon = con ();
String sqlstr = "insert into my_test select number, name, remarks from OpenRowSet ('Microsoft. jet. oledb.4.0 ', 'excel 5.0; HDR = yes; database = E: \ test.xls', sheet1 $ )";
/* This can be used*ReplaceNo., name, remarks*/
Sqlcommand cmd = new sqlcommand (sqlstr, mycon );
Mycon. open ();
Cmd. executenonquery ();
Mycon. Close ();
}
}

• This article from sugon blog http://www.ezloo.com/2008/11/excel_to_ SQL _server.html