将Excel数据快速大批量导入数据库的代码（Quickly import the Excel data into the database code）.doc
文本预览下载声明
将Excel数据快速大批量导入数据库的代码（Quickly import the Excel data into the database code）
Quickly import the Excel data into the database code
Classification: net 2011-09-09-09-423 reading review (0) collecting reports
The two ways to import data from EXCEL into SQL SERVER.
In the program, use ADO.NET. The code is as follows:
/ / connection string
String strConn = Provider = Microsoft. ;
OleDbConnection conn = new OleDbConnection (strConn);
Conn. The Open ();
DataTable dtSchema = conn. GetOleDbSchemaTable (OleDbSchemaGuid. Tables, new object [] {null, null, null, TABLE});
DataSet ds = new DataSet ();
/ / an EXCEL file may have multiple worksheets, traversing them
Foreach (DataRow Dr In dtschema.rows)
{
String table = Dr [r]. TABLE_NAME ToString ();
String strExcel = SELECT * FROM [ + table +];
Ds. Tables. The Add (table);
OleDbDataAdapter myCommand = new OleDbDataAdapter (strExcel, conn);
MyCommand. The Fill (ds, table);
}
Conn. Close ();
That way, the data is hidden in the DataSet.
In this way, the database machine does not need to be equipped with EXCEL.
2. In the query analyzer, write the SQL statement directly:
If you import data to an existing table, use it
INSERT INTO table SELECT * FROM OPENROWSET ( MICROSOFT. JET. Oledb.4.0
, Excel 5.0; HDR = YES; The DATABASE = c: \ test. XLS sheet1 $)
In the form of
If you import data and add tables, use
SELECT * INTO table FROM OPENROWSET ( MICROSOFT. JET. Oledb.4.0
, Excel 5.0; HDR = YES; The DATABASE = c: \ test. XLS sheet1 $)
In the form.
The above statement is to read all the columns in the SHEET1 worksheet in EXCEL file, and if you want only the partial columns, you can
INSERT INTO table (a1, a2, a3) SELECT a1, a2, a3 FROM OPENROWSET ( MICROSOFT. JET. Oledb.4.0
, Excel 5.0; HDR = YES; The DATABASE = c: \ test. XLS sheet1 $)
You can actually use OPENROWSET ( MICROSOFT.
, Excel 5.0; HDR = YES; DATABASE = c: \ test.xls, sheet1 $) as a table, for example, I wrote a sentence like this:
INSERT INTO eval_channel_employee (channel, emp
显示全部