将Excel数据快速大批量导入数据库的代码（Quickly import the Excel data into the database code）.docVIP

将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