A feasible method to import excel into sqlserver efficiently. Import excel into sqlserver

A feasible method to import excel into sqlserver efficiently. Import excel into sqlserver

Most people know that oledb is used to read data to dataset, But it is strange to know how to process dataset after reading data. Many people concatenate SQL statements through loops, which is not only error-prone but also inefficient. data. sqlClient. sqlBulkCopy is still unfamiliar to beginners. This is the legendary bcp with extremely high efficiency. It takes only 60 thousand seconds to import more than 4.5 pieces of data from excel to SQL.

Using System; using System. data; using System. windows. forms; using System. data. oleDb; namespace WindowsApplication2 {public partial class Form1: Form {public Form1 () {InitializeComponent ();} private void button1_Click (object sender, EventArgs e) {// test, import Sheet 1 in excel to sqlserver string connString = "server = localhost; uid = sa; pwd = sqlgis; database = master"; System. windows. forms. openFileDialog fd = new OpenFileDialog (); if (fd. showDialog () = DialogResult. OK) {TransferData (fd. fileName, "sheet1", connString) ;}} public void TransferData (string excelFile, string sheetName, string connectionString) {DataSet ds = new DataSet (); try {// obtain all data string strConn = "Provider = Microsoft. jet. OLEDB.4.0; "+" Data Source = "+ excelFile +"; "+" Extended Properties = Excel 8.0; "; OleDbConnection conn = new OleDbConnection (strConn); conn. open (); string strExcel = ""; OleDbDataAdapter myCommand = null; strExcel = string. format ("select * from [{0} $]", sheetName); myCommand = new OleDbDataAdapter (strExcel, strConn); myCommand. fill (ds, sheetName); // if the target table does not exist, create string strSql = string. format ("if object_id ('{0}') is null create table {0} (", sheetName); foreach (System. data. dataColumn c in ds. tables [0]. columns) {strSql + = string. format ("[{0}] varchar (255),", c. columnName);} strSql = strSql. trim (',') + ")"; using (System. data. sqlClient. sqlConnection sqlconn = new System. data. sqlClient. sqlConnection (connectionString) {sqlconn. open (); System. data. sqlClient. sqlCommand command = sqlconn. createCommand (); command. commandText = strSql; command. executeNonQuery (); sqlconn. close () ;}// use bcp to import data using (System. data. sqlClient. sqlBulkCopy bcp = new System. data. sqlClient. sqlBulkCopy (connectionString) {bcp. sqlRowsCopied + = new System. data. sqlClient. sqlRowsCopiedEventHandler (bcp_SqlRowsCopied); bcp. batchSize = 100; // number of rows transmitted each time (bcp. yyafter = 100; // The number of rows in the Progress prompt (bcp. destinationTableName = sheetName; // destination table bcp. writeToServer (ds. tables [0]) ;}} catch (Exception ex) {System. windows. forms. messageBox. show (ex. message) ;}}// void bcp_SqlRowsCopied (object sender, System. data. sqlClient. sqlRowsCopiedEventArgs e) {this. text = e. rowsCopied. toString (); this. update ();}}}

The above TransferData can be used directly. If you need to consider it carefully, you can use oledb to obtain the excel table structure and add ColumnMappings to set the control field, in this way, you can achieve the same effect as data transmission of sqlserver.

How to import data in Excel to SQLServer2005

Do you want the system to provide this data import function or just import data to the database.
If you only need to import data to the database, the data transmission of SQLSERVER2005 will be done ..
To provide this import function, use the jxl jar package.

How can I import a complete excel table to the SQL server2005 database?

EXECUTE (n' bulk insert Employees FROM "E: \ learning materials \ documents included in SQL SERVER 2005 \ Employees.csv"
WITH (
CHECK_CONSTRAINTS,
CODEPAGE = ''acp '',
DATAFILETYPE = ''char '',
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\ n '',
KEEPIDENTITY,
TABLOCK
);');

Or use the import/export wizard.